Alteryx Designer Desktop Discussions

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

Multi Row Formula help

wonka1234
10 - Fireball

Hi,

 

I have the following multi row formula, it creates a column named select and assigns it a 1 where my conditions are met. 0 for no conditions met.

 

if left([CMB SERIES NUMBER],4) = 'CHT1' || [CMB SERIES NUMBER] = 'Total'

then 1

else 0

endif

 

However I only want to do it up unti my FIRST total. I have two Totals in my SERIES column. How can I get my assiging of 1 to stop after my first Total?

 

Problem is I have many CHT1 below my FIRST Total that i want to exclude.

4 REPLIES 4
SPetrie
13 - Pulsar

A multi row may not be necessary here.

The way I went about solving it was to use a record ID and single out the first Total row found. I add that back on to the main data as a helper column and use it as part of the conditional to assign the 1 or 0 with a formula tool.

SPetrie_0-1658949119754.png

SPetrie_1-1658949155612.png

 

 

wonka1234
10 - Fireball

@SPetrie 

 

Still getting everything below the Total. 

 

wonka1234_0-1658949923244.png

 

Seems like your filter is getting the 2nd total in this column. Not the First.

 

SPetrie
13 - Pulsar

The red arrow on the total row indicates that there is leading or trailing white space on that cell. The filter was set to = "Total" so that will fail on that cell. Update the filter formula to be 

trim([CMB SERIES NUMBER]) = "Total" and it should work.

binuacs
21 - Polaris

@wonka1234 Multi-Row tool to achieve the result 

binuacs_0-1658958748888.png

 

Labels
Top Solution Authors