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.
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)?
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
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…!
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
