Using 'IN' within a Contains function?

I'll start off by saying I LOVE that Alteryx allows for the use of IN within tools like the Filter!

HOWEVER, I am trying to evaluate if a dataset of large string fields contains 8 Ids and I'd rather not write 8 lines of 'contains' statements- is there a trick formula for filtering n-number values within a larger string field equivalent to something like:

Contains([Field], IN ("val1","val2","val3",val4", etc)?

Tips and Tricks

Expression

Accepted answers

MarqueeCrew

All comments

MarqueeCrew

MarqueeCrew

Did it work?

aMac

Epic. Hat's off to you. Worked great!

Sidenote: I was interested to see that I was able to insert each of my strings without needing to wrap each in its own 'prime' mark

eg:

Regex_countmatches([Description2],"A030P000000MjMOQA0|A010P000000emuiQAA|A030P000000emvHQAQ")

MarqueeCrew

@aMac,

I've posted a macro to the gallery that you might be interested in: Contains In-List.

It solves for an Alteryx use case where an analyst desires to filter records based upon the presence of any one or many terms in a single field. This macro allows the user to specify a list of terms to search for in a target field. If any of the terms are found in the field, a "True" indicator is returned. The delimited values can use any chosen delimiter as long as the delimiter is a single text character.

The search terms are entered as a single delimited set of values. It is sensitive to spaces and will match any term as a whole word only. As an example, FLO followed by a SPACE will not match to FLOWER. A configuration option allows you to match without case sensitivity. Optionally, you can filter the output to only output either the Found or Not Found records.

Here are some posts to YouTube that describe the construction of the macro:

[video]

Hopefully this helps you and others.

Cheers,

Mark

@LeahK @AdamR_AYX

clant

Thank you for this @MarqueeCrew, got me out of writing a few hundred contains or.

Also thank you for your macros, they save me a hell of a lot of time on some projects!

bb213

This is great. If it's not too much trouble, could you create one that allows for "Target Terms" to be empty- thus passing everything through the 'True' filter? @MarqueeCrew

alexcordero

Hello Mark,

How can I utilize a field to update Target Terms? The terms for the contains will change month-to-month and for audit purposes are documented in an Excel tab. I'd rather the macro read terms from a column or a separate input where I can concatenate the data into the necessary format for the target terms and populate the macro. Trying to avoid the user to have to manually populate each month.

Alex

cmsteinberg

I guess I am 4 years late to the party, but I am wondering if there is a version of this macro where the input field was replaced by a file, so it can be modified by the end user as an input file for a more automated approach.

And second, other than sequencing them, what if I want to run it on 5 fields?

Comment 1

Comment 2

Comment 3

Comment 4

Comment 5

Thanks in advance.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703