Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Copy Values from the Subtotals to the Column

NicholasTan
5 - Atom

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

3 REPLIES 3
ch12345
7 - 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.

JessieC
Alteryx
Alteryx

Hi @NicholasTan ,

 

Attached is a working example of what @ch12345 described.

clipboard_image_0.png

 

Hope it helps!

 

NicholasTan
5 - Atom

Thanks so much for your prompt response! Greatly appreciated!

Labels