Iterative macro for nested hierachy

[gerbaudo]

Hello,

   I am trying to implement an iterative macro to build a nested hierarchy from a list of entries.

For example, starting from an input

Child	Parent
A	B
B	C
C	D
E	B
F	Z
Z	H

I would like to add columns containing the hierarchy for each element, and its oldest ancestor:

Child	Parent	Path	Ancestor
A	B	/D/C/B/A	D
B	C	/D/C/B	D
C	D	/D/C	D
E	B	/D/C/B/E	D
F	Z	/H/Z/F	H
Z	H	/H/Z	H

I have tried to follow the example in the "Creating a HR hierarchy" post,

but I cannot get the output I'd like.

In particular, I have defined an iterative macro with the output for the next iteration ('N')

and connected it to the input for the next iteration ('S'), but I see that after two iterations

I have multiple outputs (some duplicated), and the "Path" is two-level deep at most.

The two files (macro and workflow) are attached here.

I implemented a recursive function in python that behaves as expected,

but I cannot wrap my head around the Alteryx implementation with an

iterative macro--I must be missing something basic, sorry about this.

Any advice on what I am doing wrong, please?

Thank you.

[WayneWooldridge · Accepted solution]

Hi @gerbaudo,

I think I understand where you are going with this.  I had to restructure this a bit; take a look at the attached workflow.  What I did is think of your 'Child' field as people.  Each of these people has a parent.  The attached workflow will iterate through all the child/parent relationships to establish a genealogy line.

Pay close attention to macro itself.  You'll need to modify some settings in the macro using the View --> Interface Designer screen (click on the wrench on the left side of the window).  Specifically, you need to select your iteration input and iteration output fields. 

[gerbaudo]

Thank you so much @WayneW !

It works like a charm.

Best,

Davide

[rakhibubbly86]

Is there any way to put filter where I can specify like I want all childrens of Parent ID 4 so i can get 1,2,3

[rcruz03]

Hello,

I tried the sample and getting the results below. 

Child	Parent	Ancestor	Path
C	D	D	D:C
Z	H	H	H:Z
B	C	C	C:B
C	D	D	D:C
Z	H	H	H:Z
F	Z	Z	Z:F
A	B	B	B:A
E	B	B	B:E
B	C	C	C:B
C	D	D	D:C
Z	H	H	H:Z
F	Z	Z	Z:F

Why am I not showing the expected the results below. 

Child	Parent	Path
A	B	C:B:A
E	B	C:B:E
B	C	D:C:B
F	Z	H:Z:F