cancel
Showing results for 
Search instead for 
Did you mean: 

Break Hierarchy into multiple levels

SOLVED
Highlighted
gnans19
Alteryx Certified Partner

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 1Level 2Level 3Level 4Level 5Level 6
AB AB    
BC ABC   
CD ABCD  
DE ABCDE 
EF ABCDEF
  • Preparation
  • Transformation
Alteryx
Alteryx

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

 

Hierarchy.png

 

Thanks

 

Joe

alex
Bolide

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. 

hierarchy4.JPG

 

 

gnans19
Alteryx Certified Partner

Thank you..

Pulsar
Pulsar

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.

Bolide
Bolide

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-level...
- 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.

alex
Bolide

@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. 

Capture.JPG

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

Bolide
Bolide

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:

 

complex.png

 

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.

DataG8
Atom

The only issue that I haven't seen covered in this thread is the case where an infinite loop could occur, either by input mistake or a subtly in the relationship hierarchy.

 

The single input mistake for example is the case where Parent=Child, either an input error or a system may have that step for added descriptions during a process. This can easily be addressed by filtering and flagging where Parent=Child.

 

The second tier of this is the possibility of a sub-tour. Again, either by mistake or a nuance to a sub assembly iteration. This may be more challenging as there is a possibility that the repeat pattern could be very long. Just limiting the max iterations though and checking the ones that didn't complete may be enough in all practical applications.

Bolide
Bolide

Thanks @DataG8 ! Those sounds like good situations to capture for. Can you make up some sample data that creates those situations, and what output would you want for that sample input?