Parent-Child Hierarchy

[dimi]

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:

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

[bruno29a]

Hi @dimi , I don’t think there is a node that can do that. It would be interesting if such a node existed, may be something to consider.

This kind of “challenge” has come up a few times, and one of the ways to do this would be via a recursive loop.

[dimi]

Hi @Bruno29a,

Thanks Bruno - I used the recursive loop.

Yes I would love a nicely optimized node for this type of tasks - it is one of the basics of BI.

Thanks a lot in any case
Dimitri

[ipazin]

Hello @dimi,

this topic provides discussion on this topic:

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!

Br,
Ivan

[dimi]

Hi Ivan,

Thanks for the link! We ended up using the cell replacer like suggested in your solution, but we will probably do some scripting in our final version.

LG, Dimitri

[bruno29a]

Hi @dimi @ipazin , the request in the other thread is not exactly the same - hence why I did not propose it.

The request in the other thread finds the top parent only, while what @dimi is looking for is more of the path, meaning finding all parent → grand parent → grand grand parent → … → top ancestor. Unless the path was meant to find the top ancestor only (just like in the other thread)?

[dimi]

Hi @bruno29a @ipazin,
Yes, we ended up only extracting the top parents in Knime. We were able to build a path (using cell replacer concatenate) - but our solution was not very “elegant”. Therefore, we opted to rather use service analysis.

[ipazin]

Hello there,

indeed @bruno29a request is not same but two interesting things were mentioned in it and that’s the reason of sharing. One is KNIME Network Mining extension which might be able to do this. (I haven’t used it so can’t say it can for sure but worth exploring before going scripting @dimi.) Second one is my comment in one of replies: “…(no Rule Engines outside loop and one Rule Engine was inside loop to give entire path which led to parent)…” which shows you how to get full path. Having said that here is workflow example how to get full path. Looks decent to me @dimi.
Top parent hierarchy search_ipazin.knwf (48.0 KB)

At the end one Rule Engine is outside and seems it’s needed there but maybe it’s just too late for me

(And third interesting thing mentioned is how to build better stopping criteria )

Br,
Ivan

[tobias.koetter]

Hello @dimi ,
please have a look at this example that uses the KNIME Network Mining extension that Ivan mentioned:

Shortest Path Extraction – tobias.koetter

Bye
Tobias

[aworker]

Hello @tobias.koetter

Thanks a lot for posting this solution based on the KNIME relational network nodes. This is definitely the best way to deal with this kind of -relational graph- problems.

I would like (need) to run it on the 4.4.1 KNIME version (for the time being) but obviously it is already implemented on the 4.5.0 version lol . Thus, KNIME complains about the -Recursive Loop- nodes for which I have the extension in version 4.4.1 but apparently not compatible with the newest one 4.5.0.

Since the 4.5.0 KNIME version is really just released, would you mind please to just post here a snapshot or explain what was your -Recursive Loop End- configuration ?

Thanks in advance.

Best

Ael

[bruno29a]

I put something together that does both the Path and the Top Parent which looks like this:

Path results:

Top Parent results:

Note: The main purpose of the workflow was for retrieving the Path. But since it already compiled the data, I’m able to retrieve the Top Parent also. But I believe that, for the Top Parent, the solution in the other thread is more optimized - or perhaps the solution by @tobias.koetter could be, but I’m not able to test it as I have the same “problem” as @aworker (Knime 4.4.x vs 4.5.x)

Here’s the workflow: Parent-Child Hierarchy.knwf (30.5 KB)

[trapilly]

Hello @bruno29a,

I work with @dimi, your solution makes perfectly the job for what we are trying to do.

Thanks for you reply!

Thomas

[bruno29a]

Hi @trapilly and welcome to the Knime Community.

You are most welcome, I’m happy to help.