Using 'IN' within a Contains function?

[aMac]

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)?

[MarqueeCrew · Accepted solution]

Regex_countmatches([field],”one|two|three|four|five|six|seven|eight”)

How about that?

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

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

[MGT]

Could you please  repost the link of where to find the macro? I click on the link but it doesn't allows me to open the files.

Best, MGT

[MarqueeCrew]

Here is the macro (below).

Cheers,

Mark