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.
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.
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:
Here is a route that makes use of two iterative macros:
- 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.
@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.
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.