Mapping Hierarchy of IDs (Parent-Child relationships)

I have only the first three (3) columns from my sample data set below:

  1. Since I only have ID and its immediate parent…is there a way to ‘map-out’ the full hierarchy of a given ID? (A sample of what the result could look like is in columns 4-5-6.) I understand that variables and/or recursive loops can help me solve the problem, but I’m having trouble implementing it in a way that works.

  2. With #1 solved, I can use Rule Engines for the rest of the columns…but would need the hierarchy mapped to do this.

  • Top Record = Project ID has no parent ID (i.e., at the top of its hierarchy)
  • Is BSS = Project name starts with “BSS”
  • Is BSS Related = Project ID has a “BSS” project anywhere in its hierarchy
  • Has BSS Ancestor = Project ID has a parent ID anywhere along it ‘up-line’ hierarchy (not just its immediate parent
  • Is Top BSS = Project ID is a “BSS” project, and does not have a BSS ancestor

Might anyone have recommendations on how to implement item 1 (while looking-ahead at item 2)?

Both the full table (per the screenshot) and sample I am starting with (i.e., the 1st three columns only) are in the attached workflow export:

Sample.knwf (7.8 KB)

Appreciate any direction on how to approach this in KNIME…!

Hi there @brianpadua,

welcome to KNIME Community!

I have played a bit with it and came up with following workflow which should cover #1. Check it out and ask if any questions. It is partly documented and hopefully you will figure out where to place logic for other columns :wink:

Sample.knwf 1.knwf (47.3 KB)

Br,
Ivan

2 Likes

Hi @ipazin – thanks for taking a look at this one!!

A cursory glance ticks all boxes I had been struggling with, incl. Column Expressions (which I had discovered shortly after my OP), as well as using counter generation as a variable in a (working) loop.

One thing that I hadn’t mentioned is that the max hierarchy level is unknown, although I’ve seen it go up to 8 levels in my production data. I can probably examine the Column Expressions Switch statement to go up to 10 cases as a band-aid.

For item #2 (true/false identifiers), I plan to break-up the Full Hierarchy column and loop through each level to determine which IDs have “BSS ancensors” (and are BSS-related)… Probably not optimal, but but it should get the job done.

I’ll mark this as the solution for now, but will be open to any additional solutions/comments from the community.

Lastly, I plan to post a part 2 in the next 1-2 weeks after testing on a larger prod sample (incl. item #2 logic). I’m sure to run into additional questions by then…!

Again, thanks for your help!
Brian

1 Like

Hi @brianpadua,

glad I could help. If you are looking for more suggestions I will remove solution mark so topic doesn’t get closed and you can post your solution which I can check.

Regarding #2 I’m sure some logic you can incorporate into existing loop but it is up to you :wink:

Br,
Ivan