# Top parent hierarchy search

Hello,

I have a table of users and their parents.If the parent is 0, this parent is the top parent of a group. I want to calculate to have for each user, his/her top parent.

Example:

user | parent
1 | 3
2 | 0
3 | 4
4 | 5
5 | 0
6 | 2

Result

user | top_parent
1 | 5
2 | 2
3 | 5
4 | 5
5 | 5
6 | 2

Do you have any suggestion?

Thank you!

For me, if the parent is 0, it usually means that this user is a parentâ€¦

Can you explain how you got the results? For example, how does user 1 get 5 as top_parent?

1 Like

Yes

the hierarchy for 1 is:

5
4
3
1

Parent of 1 is 3, but 3 is not a top parent as its parent is 4. 4 is neither a top parent as its parent is 5, which is the top parent. So, top parent of 1 is 5.

However, Iâ€™m not following how you determined the hierarchy for 1 to be 5,4,3,1.

For example, for 6 | 2, what would be the hierarchy (Iâ€™m guessing by the result that it would be 2,1), and how do you determine that?

1 Like

That would be:

2
6

2 is the parent of 6 (the last row of the input table) and we know that 2 is a top parent as it is marked as 2 | 0 at the second row of the input table. So 2 is the top parent of 6 (in this case, the unique parent)

Hierarchy for 1 is 5,4,3,1 because:
1 | 3 â†’ 3 | 4 â†’ 4 | 5 â†’ 5 | 0

Is that correct?

3 is parent of 1, and 4 is parent of 3, and 5 is parent of 4 and 5 is a top parent

So, reverse hierarchy would be 1,3,4,5, is that correct? Is that how the hierarchy is determined?

EDIT: If Iâ€™m correct in the logic, then this can be done via a Recursive Loop.
I need to step out at the moment. If you have not determined how to implement this, Iâ€™ll do it later.

2 Likes

If I understood well, this is a relational graph problem (with nodes and arrows) which can be represented as follows:

So a parent of any number is the last possible number reached following the arrows (apart from 0):

1 â†’ 5
3 â†’ 5
4 â†’ 5
5 â†’ 5
2 â†’ 2
6 â†’ 2

If so, you would need to use a -recursive loop- to eventually obtain the results you want.

B.t.w. you could also use the KNIME network nodes specialized on Relational Graphs:

Hope this helps.

Best

Ael

2 Likes

Yes! Thatâ€™s right! I will implement this tomorrow. Thanks for your time

1 Like

Yes, thatâ€™s it. @bruno29a has understood also the problem. Hope you can help me as I still have not implemented any recursive loop

Please find below a possible commented solution:

and the workflow:

Hope this helps.

Best

Ael

4 Likes

I give it a look tomorrow and let you know.

Thank you

My pleasure

Best

Ael

1 Like

Hi @jquadrada , Iâ€™m back. I see what @aworker has provided solution, which is good enough.

@aworker , originally I had wanted to use H2 as a virtual table that would allow me to set some values and update them within the loop. It looks like itâ€™s no necessary based on what you did

3 Likes

WOW! Thanks @aworker ! It works great!!!

2 Likes

Thanks for your time and support @bruno29a !

1 Like

Best,

Ael

Thanks for your help here. Iâ€™m always impressed by your solutions to so many different KNIME questions!

Sorry, I did not understand what you meant here by â€śto use H2â€ť. Could you please develop ? May be you could add here this alternative solution too.

Best

Ael

Hi @aworker , thanks for the kind words.

Regarding your question about H2, manipulating values in a table and keeping these new values within loops in Knime is quite tricky, so my idea was to use a virtual db table instead where I could update values in the db table at every iteration of the loop, and H2 is the best way to do this as it can generate the db connection and create a table in memory.

However, with the Recursive Loop, it was not necessary, since the Recursive Loop allows you to pass on the â€śnewâ€ť values to the next iteration.

1 Like

Hello there!

How about simply a Cell Replacer within Recursive Loop? Also stop criteria is probably a good idea so you donâ€™t have to run large number of iterations to be sure youâ€™ll find parent.

Here is workflow:
Top parent hierarchy search_ipazin.knwf (31.5 KB)

Br,
Ivan

6 Likes

Hey @ipazin .

Damn man! Thatâ€™s so cool! Plain and simple! So fast too!
Genius!

3 Likes