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

Help with Multi Row Formula tool

HW1
9 - Comet

Could someone help me get the correct Multi-row formula?
I have a partial output as:

 

RecordIDIdentifierAmount
18  
54  
64Y 
65 37.5
66 40.5
67Y 
68 22.5
69 30.5
74 53
82  
83  

 

I want the sum of the above all values till the next "Y" in the [Identifier]

as:

 

RecordIDIdentifierAmountSum
18   
54   
64Y  
65 37.5 
66 40.578
67Y  
68 22.5 
69 30.5 
74 53108
82   
83   

 

I am trying the multirow formula tool with the logic "If [Identifier] is null, add all above non null columns from [Amount] else null but I am unable to get it right.

Help is much appreciated.
Thanks

7 REPLIES 7
apathetichell
18 - Pollux

If I may make a suggestion - it's easier to assign a distinct identifier to Y using a multi-row formula than to create a running total. So instead of summing here - have your formula say

if [row-1:recordid]=null() then 1 elseif [identifier]="y" then [row-1:identifer]+1 else [row-1:identifier] endif

 

Note - the first part tests for the first record and creates an initial value of 1. It looks at a column field which will not have any nulls() in the previous row unless the row is the first row.

 

Now - once you have this identifier add a summarize and group by that new column and sum your data.

 

 

HW1
9 - Comet

Is this the formula tool or the multi row formula tool where I will attempt this?

apathetichell
18 - Pollux

multi-row formula tool. Anytime you are looking at a previous row or future row - you use multi-row tool.

HW1
9 - Comet

I think You mean

IF IsNull([Row-1:Identifier])
THEN 1
ELSEIF [Identifier] = "Y"
THEN [Row-1:AMOUNT] + 1
ELSE [Row-1:Identifier]
ENDIF

 

I am getting 

RecordIDIdentifierAmountNew Field
18   
54  1
64Y 1
65 37.5Y
66 40.51
67Y 1
68 22.5Y
69 30.51
74 531
82  1
83  1

 

Where am I going wrong?

HW1
9 - Comet

There is no Row + 2 or Row - 2  else this can be an easier solution

apathetichell
18 - Pollux

Not sure what your fields were called when I wrote it out so I actually used "identifier" the second two times to mean the new field - sorry. 

 

This is the proper formula:

if [row-1:recordid]=null() then 1 elseif [identifier]="y" then [Row-1:newfield]+1 else [Row-1:newfield] endif

 

[newfield] is the name you use for the new column.

 

Your [amount] column was non-numeric when I pasted it so the attached workflow does the multi-row properly and converts your [amount] field to a numeric field. Then it summarizes by different "y"s.

HW1
9 - Comet

Oh good! I used your approach further to get what I want.

 

Thank you!

 

Labels