Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Rollup to topmost hierarchy

I am trying to create a workflow to find out users who fall under one particular department, and append their user ids in every row if they fall in that particular department. Like the second row should contain the users from row 1(dept A) and row 2 (Dept B which falls under dept A). The departments is a drill down hierarchy. Is there a way to generate this list using Multi row formula or any other tool?

DeptuseridDesired Output
AUser1, User2User1, User2
A:BUser3User1, User2, User3
A:B:CUser4, User5User1, User2, User3, User4, User5
A:B:DUser6, User7User1, User2, User3, User6, User7
A:B:D:EUser8User1, User2, User3, User6, User7, User8
A:FUser9User1, User2, User9
P:QUser11User10, User11



I think the easiest way is an iterative macro.



First, break userid into rows and work out what level the records are (count the : in the dept)

Then pass into an iterative macro




Each loop of the iterative macro appends the parents rows to the child. Walk down level by level.

Final concatenate the results.


Sample attached