hierarchy automation by Iterative macro

[Meghnagupta]

I have uploaded 1 file Hierarchy_Template which is Input file in this.

1st we have to put filter on Type = Profit center

Then we have the data which will pick ID.

Afterwards we need to pick "ID" column value say "74226" then we will pick it's Parent ID "43420" this will be hierarchy 1

and then we will search 43420 in "ID" column again and pick parent ID "43388" this will be hierarchy 2

again we will search "43388 in "ID" column and pick parent ID till we get P001

which searching till P001 we will remove filter of Profit Center

and then it will pick next "ID" after putting filter of Profit center

Is this possible in Alteryx?

Can someone help me creating this workflow?

[Meghnagupta]

@binu_acs could you please help me in this?

[binu_acs]

@Meghnagupta I dont see the Parent ID 43420  and 43388 in the ID column of the given input (Hierarchy tab), can you also provide the expected output

[aatalai]

@Meghnagupta could this macro help https://community.alteryx.com/t5/Community-Gallery/Hierarchy-Generation-Macro/tac-p/881903#M109

[Meghnagupta]

@binu_acs : I have attached required output.

I have added notes for your reference.

Example from attached input file:

I am gonna put filter on Type = Profit Center and pick any ID (column C) 

Let's say I pick 74226 from column C and it's parent id 43420 will be hierarchy 1

then, i am going to pick 43420 parent ID and remove filter of Profit center and search in column C
while i search in column C, i again have a Parent ID 43388
Then, again going to pick 43388 and search in column C and this loop goes till I get P001

this is for 1 and again going to pick ID from Column C with filter Profit center and repeat same action

This is the requirement

[binu_acs · Accepted solution]

@Meghnagupta I created a batch macro and an iterative macro to identify the hierarchy. They are working as expected, but the workflow takes longer to run when the record count is high, so it will require heavy optimization. I tested it with 10 records using the Sample tool. If you want to run the workflow on all records, you can remove the Sample tool and test it, but I recommend keeping the Sample tool and trying it first with a limited number of records.

[Meghnagupta]

@binu_acs : Thank you for your help. This works perfectly 

[binu_acs · Accepted solution]

@Meghnagupta Here is the optimised version of the workflow, it takes only a few seconds to complete 

[Meghnagupta]

@binu_acs : This is a great help! Thank you again

But in Iterative Macro_ID_test.yxmc what is \Iterative_Input.csv and \ID_Main.csv file you have used?

Can you share that with me as well?

[binu_acs]

@Meghnagupta sure, let me know if you face any issues with the workflow or result