Create dynamic columns to reflect company hierarchy based on company and parent IDs

Question

I have a list of approximately 2000 companies, where each company has a unique ID number in the Entity ID column and the ID of their parent company in the Parent ID column. All parent companies in the Parent ID column are listed once in the Entity ID column, and the Parent ID for the main parent company is null.

I want to create new columns to better show the hierarchy of all companies on the list where

* L1 (new) lists the main parent company ID
* L2 (new) lists the second-level parent company ID (this field should be null for the main parent company)
* L3 (new) lists the third-level parent company ID, if any

For entities where the Entity ID is the same as the level shown in the new column, all subsequent columns should have null values, and the number of new columns should be dynamic, if possible, based on the input data.

I usually can figure out a solution to my problems by perusing replies to various community posts (you all are amazing, by the way!), but I am pulling out my hair trying to figure this out. Thank you for any help you can provide!

Hierarchy Workflow.yxmd

OllieClarke · Answer

Hi @aschoenman There is a weekly challenge which is very similar to your problem - https://community.alteryx.com/t5/Weekly-Challenge/Challenge-12-Creating-an-HR-Hierarchy/td-p/36740 
In order to be dynamic, you'll probably want an iterative macro, there's a great article on them if you've not made one before - https://knowledge.alteryx.com/index/s/article/Getting-Started-with-Iterative-Macros-1583461640389

Hope that helps,

Ollie