Building a hierarchy table from transactions

[Data_consumer]

Hi Community!

I’m struggling with a workflow, and the logic on how to do it. I have a table with transactions, and this table has an investor and an investee. As the investments might have multiple levels, I would like to extract the investment structure from the transactions. Below and attached, an example of the data. Can you think of any way to archieve this?
example hierarchy.xlsx (10.0 KB)

[takbb]

Hi @Data_consumer ,

Does this thread give some ideas? Take a look and if no joy, I’ll do some head scratching.

Parent-Child Hierarchy

Dear Knime community! I would like to know whether a node exist that can create the column “path” with the columns “Child ID” and “Parent ID” as inputs: [image] This is a standard function in DAX (PowerBI /SAAS language), which is very useful for Business Intelligence applications. Thank you very much for any tips! Dimitri

[Data_consumer]

Thanks @takbb! Glad to see others have struggled with the same issue before I’ll come back if i’m not able to get it done with these instructions

[Data_consumer]

@takbb Almost there I used the Top Parent hierarchy search_Ipazin model.

Couple of issues, first being that the starting point in the results is not always the TOP level (the level that does not have any Parent IDs, in example should always start with ID, not IDC) and also, it prints the between paths also (first 2 rows in the results).

Any ideas :)?

[takbb]

Hi @Data_consumer , that’s looking good. If you are able to upload the workflow, I can take a look. My suggestion though would be that after the duplicate row filter , you establish the list of possible “top level” parents. You can do this by joining output from Duplicate Row filter (to itself), and joining on Investor = Investee, then keeping only the Left Unmatched entries. These are Investors that are not Investees and are therefore the set of “top level”.

yes… That should give you a table containing:

Investor
ID 1
ID2
ID3

and then you can do something with that such as join back your final results table and keep only where ParentID = Investor. Then a Cell Splitter to turn the Concatenate into columns, and you arrive at the solution I think (although might need some adjustments to the recursive loop… ).

---

I used that idea in my own workflow, as I was quite interested in how @tobias.koetter’s network node and component suggestion worked in his reply.

[btw I still don’t know how they work, but then again, I don’t really know how my computer works either, but I can live with it. lol]

So here is what I have

which I think works except the columns start at Level 0 instead of Level 1 [so total failure in meeting the specification! ]

hierarchy builder.knwf (64.7 KB)

Anyway, please upload your flow if you want me to take a look. Two solutions are better than one!!

[evert.homan_scilifelab.se]

I made a simple solution using splitting, grouping and joining:

This gives:

Best wishes/Evert

Building_a_hierarchy_table_from_transactions.knwf (20.3 KB)

[Data_consumer]

@takbb Super! Seems to be working exactly like I wanted! And starting from level 0 is an acceptable failure

Again, a big thanks!

[Data_consumer]

@evert.homan_scilifelab.se

itting, grouping and joining:

Thanks @evert.homan_scilifelab.se for the help! Takbb solution seems to working, but I will test your suggested one also!

[evert.homan_scilifelab.se]

Mine is maybe a bit hardwired, not as flexible to input data changes, but it works in this case.

Happy holidays/Evert

[Data_consumer]

@evert.homan_scilifelab.se

not as flexible to input data change

@evert.homan_scilifelab.se Thanks! Happy holidays to you too!