There are many potential solutions to this issue, but which is the most suitable will depend on your dataset.
One potential solution is to populate a new column with the sub total data where it appears in the original data like so:
| Data | New Column |
| Input | |
| 1 | |
| 2 | |
| 3 | |
| | |
| Subtotal A | Subtotal A |
| | |
| 4 | |
| 5 | |
Selection of the sub-total rows could require a multi-row formula, for example if the data is exactly as shown above so it checks for empty or null rows before and after a value to identify a sub-total, or it could require a regular formula to check adjacent columns, or possibly even a non-numeric value to identify that a sub-total has occurred.
Now you need to appropriately populate the column with Subtotal Data, however Alteryx works from top to bottom and you need to populate in the opposite direction.
Add a record ID field and then sort descending to reverse the data, then use a multi-row formula to populate the New Column:
if !isempty([New Column]) then [New Column] else [Row-1:New Column] endif
Then re-sort the data as ascending using the record ID. You now have a datatable that looks like this:
| Input | New Column |
| 1 | Subtotal A |
| 2 | Subtotal A |
| 3 | Subtotal A |
| | Subtotal A |
| Subtotal A | Subtotal A |
| | Subtotal B |
| 4 | Subtotal B |
| 5 | Subtotal B |
| 6 | Subtotal B |
| | Subtotal B |
| Subtotal B | Subtotal B |
Further cleansing will depend on how the Subtotal value was identified. These additional data rows (the rows containing the subtotal in the input column or nothing) could be filtered, made blank using a formula or multi-row formula or other means.
