Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
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
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...

Adam Riley
Principal Software Engineer
Tech Lead Core Engines, Alteryx
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

Highlighted
ACE Emeritus
ACE Emeritus

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

Alteryx Certified Partner
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

Highlighted
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

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

 

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
Principal Software Engineer
Tech Lead Core Engines, Alteryx
Highlighted
8 - Asteroid

Good solutions and explanations, thanks! :)

Labels