Find the Ultimate Parent

[Kirsten]

So I've had a good hunt around and I hope I'm posing a new problem here. I have a large amount of data where some records have a ParentID and other do not. What I need to do is find the Ultimate ParentID for every record, whether there are no hierarchical levels or 100+. E.g.

ChildID	ParentID	UltimateID
A		A
B	A	A
C	B	A
D	C	A
H		H
L		L
M	L	L
U		U
V	U	U
W	V	U
X	W	U
Y	X	U
Z	Y	U

Whilst I've been using Alteryx for over a year now, I've not needed to build my own macros yet and I think I've jumping in at the deep end with my attempts on this so any help would be gratefully received!

[pedrodrfaria]

Hi @Kirsten 

I attached this example you provided using an iterative macro to accomplish this. See below:

I updated the Parent ID field to be the ultimate ID:

[jamielaird]

@NicoleJohnsonhas a really great post on this which might be useful if you want to get to grips with how to build this for yourself: https://community.alteryx.com/t5/Engine-Works/Building-a-Hierarchy-With-Kevin-Bacon/ba-p/453715

[AkimasaKajitani · Accepted solution]

Hi @Kirsten 

This is the Standard Iterative Macro Problem.

I have made the workflow for you.

Workflow:

Iterative Macro:

How to make the Iterative macro, you can learn the below URL(Interactive Lesson).

https://community.alteryx.com/t5/Interactive-Lessons/Creating-an-Iterative-Macro/ta-p/657925

[Kirsten]

@pedrodrfaria Ingenious use of Find & Replace, that hadn't even occurred to me!! Unfortunately it didn't cope with the 1.8m rows I had to run this against tho. Will certainly keep it for reference as I'm sure it'll help me solve future problems, thanks.

[Kirsten]

@AkimasaKajitani  Thanks for the fast reply, this works a treat and even coped with the 1.8m rows it needed to work through!!

[Kirsten]

@jamielaird Thanks for pointing me to that post by @NicoleJohnson, such a clear and descriptive post. Unfortunately, due to the fact a Child may have over 100+ levels to its Ultimate Parent, the solution wasn't really suited to my data. Definitely keeping a note for future problems tho!