Break Hierarchy into multiple levels

Breaking my head for past few days with cross tab, transpose, multi row formula, macros, join, union..

P.S: I want it to be dynamic(There could be additional levels). Hence I cannot use text to columns and hard coded multirow formula here.

Input		Output
Parent	Child	Level 1	Level 2	Level 3	Level 4	Level 5	Level 6
A	B	A	B
B	C	A	B	C
C	D	A	B	C	D
D	E	A	B	C	D	E
E	F	A	B	C	D	E	F

Transformation

Preparation

Accepted answers

JoeS

Please find attached a workflow I built out that hopefully achieves the result you are after.

Thanks

Joe

Create Hierarchy.yxmd

alex

I worked up a different way that utilizes a hierarchy table. This would be useful in a HR org chart or business unit structure(parent, subsidiary, division, etc.) type of analysis. In this case, you would not need to already have the data in hierarchy order to work properly. In the end the solution depends on what the actual data represents.

Hierarchy_Levels_with_keys.yxmd

Joe_Mako

Maybe I am missing something, but to me, all three of current above workflows seem to have faulty logic, and when I tried, did not work with a slightly more complex, but what I would consider a realistic data set.

What if we had multiple Level 1's or multiple hierarchies ending at different levels, something like:

complex input.png

Here is a route that makes use of two iterative macros:

Break Hierarchy.png

- RecordID to ensure the sort at the end is the same
- Join,Formula,Union to prime it an initial level
- Parent Child to Level, that is a reuse of the macro from: https://community.alteryx.com/t5/Data-Preparation-Blending/use-of-iterative-macros-to-allocate-levels-in-the-data/m-p/38197/highlight/true#M7150
- Parent Child All Levels, a macro that works from max lavel backwards to add records for each parent level
- Filter,Formula,Union to add records for Level 1
- Cross Tab to reshape to final form (uses an Integer field for the Header so it sorts correctly)
- Dynamic Rename to add "Level " to each field name

There is likely a way to get this down to a single iterative macro, and still produce correct results for more complex situations.

Break Hierarchy.yxmd

Parent Child All Levels.yxmc

Parent Child to Level.yxmc

Joe_Mako

Thanks @alex ! I took the time to create a more general solution, and used the following data set as a more complex situation to solve for:

Here is the attached workflow and macro:

Break Hierarchy v2.png

- Join to get the records where the Parent is not a Child in any other record

- Formula to prime the Path field, that will be a running concatenation of all potential paths from those starting Parents

- Parent Child Path macro:

- - Macro Input Loop In is the running Path so far

- - Macro Input Data is the complete list Parent Child pairs

- - Join the two on Child to Parent

- - Macro Output to send the know Path to the Results

- - Formula to concatenate in the new know child to the running Path

- - Macro Output the new running Path to be looped back in

- RecordID to generate a PathID

- Text to Columns to split the Path into rows

- Multi-Row Formula to get an incriminating Level number for each node in the path

- Cross Tab to reshape

- Dynamic Rename to add the Level prefix to the field names

Can you find a reasonable situation where this does not work for? Maybe this approach can be improved on.

Parent Child Path.yxmc

Break Hierarchy v2.yxmd

All comments

JoeS

Please find attached a workflow I built out that hopefully achieves the result you are after.

Thanks

Joe

Create Hierarchy.yxmd

alex

Hierarchy_Levels_with_keys.yxmd

gnans19

Thank you..

JohnJPS

I added additional data to the input including some that auto-sorting would throw off, and came up with the attached workflow to deal with it all. I ran out of gas at the end, but it has the first and last level in their appropriate spots. Fun, tricky problem.

heirarchyPairsToMultipleLevels.yxmd

Joe_Mako

What if we had multiple Level 1's or multiple hierarchies ending at different levels, something like:

complex input.png

Here is a route that makes use of two iterative macros:

Break Hierarchy.png

There is likely a way to get this down to a single iterative macro, and still produce correct results for more complex situations.

Break Hierarchy.yxmd

Parent Child All Levels.yxmc

Parent Child to Level.yxmc

alex

@Joe_Mako- you are correct that my solution does not work with your data set. Like I mentioned "In the end the solution depends on what the actual data represents." Or it can only be as good as the data given to us in the first place. Your solution is more complete where there is no hierarchy table, but does not handle situations where, either by accident or design, a child reports to 2 parents. Not calling you out, your solutions rock!, but wanted to see if you had any ideas of how to handle this for this scenario.

I've been in a couple companies where I had a dotted line to both operations and accounting or internal management and company ownership.

Joe_Mako

Thanks @alex ! I took the time to create a more general solution, and used the following data set as a more complex situation to solve for:

Here is the attached workflow and macro:

Break Hierarchy v2.png

- Join to get the records where the Parent is not a Child in any other record

- Formula to prime the Path field, that will be a running concatenation of all potential paths from those starting Parents

- Parent Child Path macro:

- - Macro Input Loop In is the running Path so far

- - Macro Input Data is the complete list Parent Child pairs

- - Join the two on Child to Parent

- - Macro Output to send the know Path to the Results

- - Formula to concatenate in the new know child to the running Path

- - Macro Output the new running Path to be looped back in

- RecordID to generate a PathID

- Text to Columns to split the Path into rows

- Multi-Row Formula to get an incriminating Level number for each node in the path

- Cross Tab to reshape

- Dynamic Rename to add the Level prefix to the field names

Can you find a reasonable situation where this does not work for? Maybe this approach can be improved on.

Parent Child Path.yxmc

Break Hierarchy v2.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335