# Alteryx Designer

Andy Uttley, Alteryx ACE, makes music with Alteryx | Math + Music
SOLVED

## Multi-Row Formula Dynamic Num Rows

Highlighted
8 - Asteroid

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

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...

Principal Software Engineer
Highlighted
Alteryx Certified Partner

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

Highlighted
ACE Emeritus

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

Highlighted
Alteryx Certified Partner

But I agree with @AdamR, 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

8 - Asteroid

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! :)

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
Highlighted
Alteryx

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.