Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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)?

 

 

14 REPLIES 14
MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
MarqueeCrew
20 - Arcturus
20 - Arcturus
Did it work?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
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
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
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
7 - Meteor

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
20 - Arcturus
20 - Arcturus

Here is the macro (below).

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels