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

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.

1 Like

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

Hello @dimi,

this topic provides discussion on this topic:

Br,
Ivan

1 Like

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

1 Like

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)?

1 Like

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.

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 :sleeping:

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

Br,
Ivan

2 Likes

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

Bye
Tobias

6 Likes

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 :sweat_smile:. 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

1 Like

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

Path results:
image

Top Parent results:
image

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)

9 Likes

Hello @bruno29a,

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

Thanks for you reply!

Thomas

3 Likes

Hi @trapilly and welcome to the Knime Community.

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

1 Like

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