community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Copy Values from the Subtotals to the Column

Hi All, 

 

I have a data set that is riddled with subtotals which shows the growing of data which I would like to retain, e.g.

 

Is there a way to obtain to get the output using a Multi-row formula or other means?

 

Thanks in advance!

 

clipboard_image_0.png

Meteor

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:

 

DataNew Column
Input 
1 
2 
3 
  
Subtotal ASubtotal 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:

 

InputNew Column
1Subtotal A
2Subtotal A
3Subtotal A
 Subtotal A
Subtotal ASubtotal A
 Subtotal B
4Subtotal B
5Subtotal B
6Subtotal B
 Subtotal B
Subtotal BSubtotal 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.

Alteryx
Alteryx

Hi @NicholasTan ,

 

Attached is a working example of what @ch12345 described.

clipboard_image_0.png

 

Hope it helps!

 

Thanks so much for your prompt response! Greatly appreciated!

Labels