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!
Hi @jquadrada , I am not sure I understand the logic…
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?
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.
Hi @jquadrada , thanks for the additional info.
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?
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)
Thanks again @jquadrada . So let’s see if I understood correctly.
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.
Hi @jquadrada & @bruno29a
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
Is this what you mean @jquadrada ?
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
Yes! That’s right! I will implement this tomorrow. Thanks for your time
Yes, that’s it. @bruno29a has understood also the problem. Hope you can help me as I still have not implemented any recursive loop 
Hi @jquadrada
Please find below a possible commented solution:
and the workflow:
20210929 Pikairos Top parent hierarchy search.knwf (74.2 KB)
Hope this helps.
Best
Ael
I give it a look tomorrow and let you know.
Thank you
My pleasure 
Best
Ael
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 
WOW! Thanks @aworker ! It works great!!!
Thanks for your time and support @bruno29a !
Hi @bruno29a
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.
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
Hey @ipazin .
Damn man! That’s so cool! Plain and simple! So fast too!
Genius!
!