Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

General Discussions has some can't miss conversations going on right now! From conversations about automation to sharing your favorite Alteryx memes, there's something for everyone. Make it part of your community routine!

SOLVED

Using 'IN' within a Contains function?

aMac
8 - Asteroid

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
19 - Altair
19 - Altair
Regex_countmatches([field],”one|two|three|four|five|six|seven|eight”)

How about that?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
MarqueeCrew
19 - Altair
19 - Altair
Did it work?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
aMac
8 - Asteroid

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
19 - Altair
19 - Altair

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
clant
8 - Asteroid

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
8 - Asteroid

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
6 - Meteoroid

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
6 - Meteoroid

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
19 - Altair
19 - Altair

Here is the macro (below).

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels