This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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]?
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.
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.
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.:
Here the formula would output if that customer number had yellow or not
HasYellow (Because the same customer number has yellow on another row)
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.
Adam Riley Principal Software Engineer Tech Lead Core Engines, Alteryx