community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
New Data Science Blog

Check out the latest post: All Models Are Wrong

READ MORE
Announcement | We'll be doing maintenance between pm 4- 6pm MT, which may impact your experience. Thanks for your patience as we work on improving the community!

Rollup to topmost hierarchy

Highlighted

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
PUser10User10
P:QUser11User10, User11

 

 

I think the easiest way is an iterative macro.

2018-12-11_16-30-00.png

 

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

Then pass into an iterative macro

 

2018-12-11_16-30-23.png

 

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

Final concatenate the results.

 

Sample attached

Labels