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

Multi-Row Formula Dynamic Num Rows

PeterPetersen
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")
7 REPLIES 7
AdamR_AYX
Alteryx Alumni (Retired)

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

Adam Riley
https://www.linkedin.com/in/adriley/
BenMoss
ACE Emeritus
ACE Emeritus

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
15 - Aurora

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
ACE Emeritus
ACE Emeritus

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

 

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

 

RoleCustomer NumberHere the formula would output if that customer number had yellow or not
Yellow4310HasYellow
Blue6600Doesn'tHaveYellow
Red4310HasYellow (Because the same customer number has yellow on another row)
Red6600

Doesn'tHaveYellow

AdamR_AYX
Alteryx Alumni (Retired)

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:

 

HasYellow.png

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.

Adam Riley
https://www.linkedin.com/in/adriley/
PeterPetersen
8 - Asteroid

Good solutions and explanations, thanks! :)

Labels