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:
- Look down the [Row type] column until you reach a row containing "Subtotal_brand", then copy the value for [Brand] from that subtotal row into every row above it up to but not including the "Subtotal_brand" row above.
- Similarly, look down the [Row type] column until you reach a row containing "Subtotal_department", then copy the value for [Department] from that subtotal row into every row above it up to but not including the "Subtotal_department" row above.
- Create new columns [Weight for brand and department] and [Weight for department only]
- For each combination of [Brand] and [Department], copy the subtotal weight figure into the relevant column.
Some thoughts from me if at all relevant:
- For steps 1,2 and 4, I think the concept is the same in that I'm trying to look down an indeterminate number of rows below/ahead before stopping and doing something. I don't think multi-row formulas can do this - not sure if iterative macros can or if there's a much simpler way to go about this. Saw this post and thought I'm kind of doing the same thing but from the opposite direction (bottom to top rather than top to bottom)
- I've also thought about maybe creating my own lookup table where I pluck out just the subtotal rows (bearing in mind I have unique row IDs I put in earlier to hang onto) and then somehow create ""filler"" rows in that lookup table to then join back into the main data. Haven't quite worked this approach out in my head but if there's a much more elegant way to do this, I'll take it. Stuff I've looked at but will revisit if this ""filled up"" lookup table might work:
Many thanks!
