Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Grouping data by hierarchical values in one column

jensroy
9 - Comet

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.

 

11.png

 

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.

5 REPLIES 5
LordNeilLord
15 - Aurora

How about this using the MultiRow Formula tool:

 

2017-06-28_12-56-57.png

jensroy
9 - Comet

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.

patrick_digan
17 - Castor
17 - Castor

@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).

Joe_Mako
12 - Quasar

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

jensroy
9 - Comet

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

Labels