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

Formula that tests the sum of 2 fields against all fields of another column

AkisM
10 - Fireball

I have a table that looks like this:

 

Amount          Totals       

234.5              4214              

400                 266.3

300                 351

234.5              235

[Null]               377

[Null]               388

[Null]               634.5

[Null]               123

[Null]               534.5

 

And after applying a certain formula(s), I want it to look like this:

 

Amount          Totals           MatchesAsSum             ReOrdered

234.5              4214            No                                 634.5

400                 266.3           No                                 [Null]

300                 351              No                                 534.5

234.5              235              No                                 [Null]

[Null]               377              No                                 [Null]

[Null]               388              No                                 [Null]

[Null]               634.5           Yes                               [Null]

[Null]               123              No                                [Null]

[Null]               534.5           Yes                               [Null]

 

Basically what the formula is supposed to be doing is this:

If CurrentRow[Amount]+NextRow[Amount] is equal to any of the amounts in [Totals], then put the value of that [Total] to CurrentRow[ReOrdered], and put "YES" under [MatchesAsSum], at the matching row of [Totals]. This will be done for Row0+Row1, Row1+Row2, Row2+Row3, etc.

 

Is this possible? Thanks in advance.

 

3 REPLIES 3
JosephSerpis
17 - Castor
17 - Castor

Hi @AkisM  Yes that is possible using the Multi-Row Formula tool.

AkisM
10 - Fireball

Hi @JosephSerpis ,

 

I thought as much. Basically I'm looking for help with the expression itself. I was thinking something along the lines of:

 

(Field to update set as ReOrdered)

If Contains([Totals], (To.String(ToNumber(Row0[Amount])+ToNumber(Row+1[Amount])))) then [Totals] else Null() endif

but apparently I can't put a formula inside contains. And I still don't know how to tell it to put "YES" at the row of the matching value, in the [MatchesAsSum] column. Because I have no way of knowing where the match occurred. And I don't know if the multi row formula tool lets you dynamically change which field will be updated. For example I don't think the tool lets you say Row(variable number)[MatchesAsSum] = "YES".

 

Thanks.

JosephSerpis
17 - Castor
17 - Castor

Hi @AkisM 

                  I created a workflow that creates the output you describe in your post. Essentially I use the multirow to create a total field then use the append to check across every total amounts then filter on those that do and join back to the original data.Multi_Row_Check.JPG

Labels