Read a hierarchical

[lug]

Hello the community,

I don’t know how to aggregate cells from an excel file. Here is an example of the source file:
|Level|Name|Quantity|
|1 |D100 |1 |
|2 |D351 |1 |
|2 |D378 |2 |
|3 |D255 |1 |
|3 |D412 |1 |
|4 |D002 |3 |
|2 |D222 |5 |
|1 |D201 |1 |
|2 |D328 |1 |
|2 |D333 |2 |
|1 |D419 |1 |
|1 |D643 |1 |

I would like to create a column with the path for each row (the path is starting from level 1) and a column with the parent like in the example below. Of course the hierarchy can be different from the example and the number of levels can exceed 4 (until 8 or 10 max)

|Level|Name|Quantity|Path |Parent|
|1 |D100|1 |D100 |D100|
|2 |D351|1 |D100/D351 |D100|
|2 |D378|2 |D100/D378 |D100|
|3 |D255|1 |D100/D378/D255 |D100|
|3 |D412|1 |D100/D378/D412 |D100|
|4 |D002|3 |D100/D378/D412/D002|D100|
|2 |D222|5 |D100/D378/D412/D222|D100|
|1 |D201|1 |D201 |D201|
|2 |D328|1 |D201/D328 |D201|
|2 |D333|2 |D201/D333 |D201|
|1 |D419|1 |D419 |D419|
|1 |D643|1 |D643 |D643|

Would it be possible to help me ? I haven’t suceeded in finding the solution

Thank you

[HansS]

Hi @lug

I assembled some KNIME nodes, and created this workflow read_hierachical.knwf (87.1 KB)
It almost gives the same output as in your example. Except for this row:
|2 |D222|5 |D100/D378/D412/D222|D100|
where I got: D100/D222/D412/D002

So check the outcome carefully. Hopefully (parts of this) workflow helps you to solve your problem .
gr. Hans

[lug]

Hello,

Thank you for this example. The line you “|2 |D222|5 |D100/D378/D412/D222|D100|” is correct in my example. The first column, “level” means that the parent is necessarely a level 1 that means the first row with value 1 above the current line. If the level is 3 it means the parent is necessarily the first level 2 above the current line, etc…

I will try to adapt it. If you have any ideas in the meantime, let me know

Thank you for your support

[ipazin]

Hi there @lug,

but shouldn’t the problematic row start with 4 and not 2? Anyways here is similar topic that might give you some ideas but in general this is not easy to accomplish in my opinion:

Mapping Hierarchy of IDs (Parent-Child relationships)

I have only the first three (3) columns from my sample data set below: [image] Since I only have ID and its immediate parent…is there a way to ‘map-out’ the full hierarchy of a given ID? (A sample of what the result could look like is in columns 4-5-6.) I understand that variables and/or recursive loops can help me solve the problem, but I’m having trouble implementing it in a way that works. With #1 solved, I can use Rule Engines for the rest of the columns…but would need the hierarchy …

Br,
Ivan

[lug]

I seems similar. I could do something with this example. Thank you.