Grouping data by hierarchical values in one column

[jensroy]

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.

[LordNeilLord]

How about this using the MultiRow Formula tool:

[jensroy]

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]

@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 · Accepted solution]

Attached is another route:

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]

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