Multi-Row Formula Dynamic Num Rows

PeterPetersen

Hi all you beautiful Alteryx people,

I'm using the Multi-Row Formula to look through a dynamic number of rows to check if a row contains a certain text. My issue is that depending on the data I might need to check anywhere between 1-200 rows. I can select many hundred rows using the Num Row variable but it seems like a weird way to do it. Then I have to change the below formula that I'm using to repeat the *** Contains("Yellow", [Row-15:Role]) OR*** up to -200 and +200. I'm using group by [variable] to determines which rows to check. Can't I rell the tool to check ALL rows with certain [variable]?

IIF(

Contains("Yellow", [Row-15:Role]) OR

Contains("Yellow", [Row-14:Role]) OR

Contains("Yellow", [Row-13:Role]) OR

Contains("Yellow", [Row-12:Role]) OR

Contains("Yellow", [Row-11:Role]) OR

Contains("Yellow", [Row-10:Role]) OR

Contains("Yellow", [Row-9:Role]) OR

Contains("Yellow", [Row-8:Role]) OR

Contains("Yellow", [Row-7:Role]) OR

Contains("Yellow", [Row-6:Role]) OR

Contains("Yellow", [Row-5:Role]) OR

Contains("Yellow", [Row-4:Role]) OR

Contains("Yellow", [Row-3:Role]) OR

Contains("Yellow", [Row-2:Role]) OR

Contains("Yellow", [Row-1:Role]) OR

Contains("Yellow", [Role]) OR

Contains("Yellow", [Row+1:Role]) OR

Contains("Yellow", [Row+2:Role]) OR

Contains("Yellow", [Row+3:Role]) OR

Contains("Yellow", [Row+4:Role]) OR

Contains("Yellow", [Row+5:Role]) OR

Contains("Yellow", [Row+6:Role]) OR

Contains("Yellow", [Row+7:Role]) OR

Contains("Yellow", [Row+8:Role]) OR

Contains("Yellow", [Row+9:Role]) OR

Contains("Yellow", [Row+10:Role]) OR

Contains("Yellow", [Row+11:Role]) OR

Contains("Yellow", [Row+12:Role]) OR

Contains("Yellow", [Row+13:Role]) OR

Contains("Yellow", [Row+14:Role]) OR

Contains("Yellow", [Row+15:Role])

,"True","False")

JohnJPS

Hi @PeterPetersen,

With problems like these, it's often a solution to just do Contains("Yellow"), and then use cross-tab and/or transpose to group and summarize in order to decide whether or not any given row gets the Yes/No desired.  With a more detailed use-case we might be able to provide a more detailed specific solution.

Another approach is to use R to write a straight-on "for" loop, which can dynamically look back to any previous row.  However for your case I would pursue the first option as far as I could before moving to for loops in R.

Hope that helps.

John