Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More

Grouping data by hierarchical values in one column

Alteryx Partner

I am stuck at something that seems should be very simple to solve, to group data by parent value in the same column.

I would like one Column per value in the column "Breakdown". The column named "Breakdown_level" indicates which level of a hierarchy the row is in. There will be a variable number of breakdown_levels, as well as variable values in the "Breakdown" column.




Any ideas on making this data more database friendly would be great!


See sample of the data i have and where i want to end up in the attachment.

Alteryx Certified Partner

How about this using the MultiRow Formula tool:



Alteryx Partner

That could work, but I failed at that. The reason i failed was that i never knew how many extra columns i would end up with, so i could not put in 3 multi-row forumals to handle 3 new columns in the event that the next time it ran it would have 5 or only 2 breakdowns. Also the names "Test" and "experience" might be something totally different the next times. "Experience" might say "Refering domain" or "Order ID". So i would need to make some ID_values on the variables.

@jensroy I'm attaching my attempt. I hope it gets you very close to what you need. I have a multi macro that I built that combines the multi-row and multi-field tools plus adds some multi-column functionality. The one thing I got hung up on was how to eliminate some of the rows that may not be needed anymore (for example I don't think you want to keep the first 2 rows). You may also have to tweak the logic for what happens when you move backwards (like from level 2 to 1 to 0).


Attached is another route:

Breakout Level Value.png


In the Macro:
- Macro Input set to Show Field Map
- Filter to keep only the current Iteration Level number
- Select to keep only relevant fields and rename with underscore (for replacement later)
- Join Multiple so all records are kept as the two new fields are added in
- Sort to ensure proper sort
- two Multi-Row Formula tools to fill in the values where row's level is greater than the current iteration
- Dynamic Rename to replace the underscores with the Level/Iteration number
- Filter to send out the completed records, and Loop back in the remaining level rows

Alteryx Partner

This worked like a charm! Using iteration runs as column name was a nice way to solve this. Thanks!