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")

[AdamR_AYX]

Can you not just use a standard formula tool to run your formula against all of the rows? I feel I might be missing something in your requirements though...

[BenMoss]

Here's how I would go about this task.

RecordID your data.

Then use the generate rows tool, lets call this 'JoinRecordID'

Set the initial expression to -200 (or however many you want to look back), and then the condition expression to be JoinRecordID <= 200. Finally the Loop Expression should be JoinRecordID + 1

Then, perform a join of 'JoinRecordID' against 'RecordID' and then you can use a filter tool to create your contains statement.

Give it a try and see if you get stuck!

Ben

[JohnJPS · Accepted solution]

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

[BenMoss]

But I agree with @AdamR_AYX, in the use-case you have given there is no reason not to just use a standard formula. I guess there are some complexities you have not outlined, in which case the solution provided by myself might be useful.

Ben

[PeterPetersen]

NOTE: I was too quick to answer. I though only Adam had replied but can see now that there are many many useful replies. I don't know how to delete this post so will just leave it for now. But I will look at the suggestions and I'm sure something will work. I still would be interested to know how this is done in Alteryx. I'm not that proficient with the formula tool. Thanks! :)

Hi Adam,

How would I do that? I would want to check whether any of the other rows contained the text "Yellow", but I would only want to look in the ones with the same customer number. I.e.:

Role	Customer Number	Here the formula would output if that customer number had yellow or not
Yellow	4310	HasYellow
Blue	6600	Doesn'tHaveYellow
Red	4310	HasYellow (Because the same customer number has yellow on another row)
Red	6600	Doesn'tHaveYellow

[AdamR_AYX · Accepted solution]

Hi Peter,

Let's start building a workflow so you can tell me if I understand your problem correctly.

I've attached what I have so far which looks like this:

So we run the formula against all rows to look for "Yellow" in the Role field. We then summarise by CustomerNumber and take the max of ContainsYellow. This tells us if a customer has yellow in any of their rows. We then join this field back onto our original dataset.

I've attached the workflow.

[PeterPetersen]

Good solutions and explanations, thanks! :)