Break Hierarchy into multiple levels

[gnans19]

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

[DataG8]

Thanks @Joe_Mako ! I guess my suggestion of a problem might not be as helpful as a solution :) I've taken your workflow and broken it into the 3 problems I had listed that you might come across in a parent-child mapping problem. I'm still pondering a solution to the 3rd. Although I feel that my proposed problems are beyond the original ask now.

The solution to the first issue of Parent=Child is the same for both examples without more knowledge of the data.

Process Issue: Might be a descriptor such as Part I in transit, needs rotation, drying time, etc.Typo Issue: Appears that the L->I relationship may have been typed I->I, this leaves L->P as a single relationshipSimple solution: Best thing to do, without more relationship data to investigate

With both of those examples, without further data or investigation, the approach is just to simplify and highlight.

I've added the sub-tour example to the workflow. I'm still trying to research a solution approach. The problem is that the workflow needs to look for a repeat pattern. The real application example is that a part/person/widget/process may return to I after having been through L and P but there are no descriptors that differentiate visit 1 to I and visit 2 to I. 

Repeat use of node I. Pattern could be drawn in loop. I->P->L repeats

[felixcaspari]

@Joe_Mako - this is great solution- thank you for sharing! 

When I try to recreate the issue with my own data set i get an error message ("Iteration #2: Tool #7: The field schema for the output "Results" changed between iterations. Parent Child Path"), do you know what might be causing this? I haven't used Macros in Alteryx before so I am wondering if you could indicate what I might be doing wrong.

I was also trying to re-build the "Parent Child Path" in the same view, but didn't understand the "Loop In L Input" & "Data D Input" references (neither did i get the "Results R Output" & "Loop Out L Output")- I am sure that ties back to the error message I am seeing in the main workflow?

Any feedback and/or inside you are willing to share would be greatly appreciated- thank you in advance for the support.

Sincerely,

Felix 

[Joe_Mako]

@felixcaspari no worries on missing it, it is a kind of hidden configuration. From the view menu, you can open the Interface designer window, and then in the configuration/properties, you can check the box or radio option that you need.

See https://help.alteryx.com/current/InterfaceDesigner.htm?tocpath=Apps%20and%20Macros%7C_____3 for more details.

[felixcaspari]

@Joe_Mako - thank you for sharing the developed solution. I came across another scenario which turned into a roadblock- we have certain instances were the relationship to "Level 1" is not always identified or in some instances doesn't even exist. I am curious how you would tackle this problem?

In theory, it appears to be a simple issue, as the "Child Tier" column should supersede the other criteria that was established in your post. With that being said, I was not able to execute that maneuver in Alteryx. Any support or feedback would be greatly appreciated

[Joe_Mako]

@felixcaspari in the attached I added a few select tools to clean up the extra column, changed the formula that computed the initial Path, and added a sort tool.

here is the new initial Path formula:

[Parent]+PadLeft("", [Child Level]-1, "|")+[Child]

[felixcaspari]

@Joe_Mako-thank you! That did the trick- and more importantly I am starting to understand the concept you used for both solutions. Thank you for sharing.

Sincerely,

Felix

[felixcaspariMM]

I think this question is similar in nature. But somewhat different as the number of steps in the hierarchy don’t align across the board (i.e. one hierarchy might have six tiers and the next one only has four). Here is a mock of what I am looking for

Current
Tier 1	Tier 2	Tier 3	Tier 4	Tier 5	Tier 6
1	B	C	D	E	F
2	E	F
3	F
4	C	D	E	F
5	H	I	F

Desired Output
Tier 1	Tier 2	Tier 3	Tier 4	Tier 5	Tier 6
1	B	C	D	E	F
2	E	E	E	E	F
3	F	F	F	F	F
4	C	C	D	E	F
5	H	H	H	I	F

Here is some additional information:

• Tier 1 will always start with a numeric value
• “F” will always be the highest tier
• Tiers are in the appropriate order

Do you think the shared solution could be tweaked to solve the described problem? Thank you in advance for the support.