Building a hierarchy table from transactions

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? :pray:
example hierarchy.xlsx (10.0 KB)

Hi @Data_consumer ,

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

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

1 Like

@takbb Almost there :slight_smile: 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 :)?

1 Like

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:

ID 1

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! :wink: ]

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!!


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)


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

Again, a big thanks! :slight_smile:

1 Like

Thanks for the help! Takbb solution seems to working, but I will test your suggested one also! :slight_smile:

1 Like

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

Happy holidays/Evert Thanks! Happy holidays to you too! :slight_smile:

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.