Hi everyone
I've got a data source I'd like to transform and I'm stuck as to where to even start so any thoughts or guidance appreciated.
I have a data source that was output from a very old system which includes "standard" data rows and subtotal rows for two different groupings (I've called them "Brand" and "Department" in this example). Apparently there is no way to change the output from the system, so I'm hoping Alteryx can do the job. I have already managed to label up all subtotal rows and remove some extremely unhelpful repeated header rows and now I'm stuck!
The subtotal rows are not purely subtotal rows I can drop because they contain one extra bit of information not included in the "standard" data rows, being weight (so it's subtotals of weight by Brand or by Brand and Department), so I need some way to access the weight values after I've transformed the data and preferably deleted all subtotal rows.
I would like to copy the Brand and Department values "upwards" from the subtotal rows onto the standard data rows, and then to populate some new columns for Weight and then drop the subtotal rows entirely. This way I'm hoping I can make my own subtotals later (e.g. with Summarize with SUM and MIN as needed given how the weight wasn't included at the standard data row level).
Here's how I've conceptualised the steps and where I think I'm stuck. I've also attached an Excel file with some dummy data for the Before data and the Step 1-2 and Step 3-4 outputs.
For each "standard" row:
Some thoughts from me if at all relevant:
Many thanks!
Solved! Go to Solution.
First I sorted on RecordID descending. Then I used the Multi-Row Formula tool to fill down each column that needed it -- Brand, Department, and Weight. Next I resorted on RecordID ascending. Then I finished it off by filtering out where Row type contains "Subtotal."
😲🤣😲🤣😲🤣
@Prometheus your speed and simplicity is astounding to me - figuratively turning the problem on its head!
Thanks very much and I will definitely remember this in future for all those pesky subtotalled reports...