How to have better substring filter

[marlline141]

Hi Everyone,

I try to filter a list based on job title such as IT. It is very annoying by pulling Credit.. any word containing it but I actually look for it director, it manager only.

Please kindly share your thought to make it easier. Thank you very much!!

[DiganP]

@marlline141 You want to use this function: [Field1] in ('IT')

Attached is the workflow.

Before: 

After:

Does this help?

[marlline141]

@DiganP 

Thanks for helping out. But it does not work. When I try lowercase([Job Title]) IN ('it'), IT Manager will not be picked up. Could you explain why?

[DiganP]

@marlline141 Its looking for that exact match with the IN function. If you want to include a lowercase 'it', you can use this.

[Field1] in ('IT', 'it')

[echuong1]

Will the job titles with IT always be multiple words? If so, you can use a filter tool and search for instances of " IT" and "IT ". See attached for an example workflow. 

[marlline141]

I tried couple of examples but IN function does not seem to work.

[lmorrell · Accepted solution]

Hi @marlline141 

Workflow is attached 

I can see lots of great solutions in this thread, but I've had a similar problem in the past so I'll adapt that solution here. It might be a little overkill, but perhaps a RegEx statement in a Filter Tool checking that there are no letters linking to 'IT' can solve the issue?

regex_match([Field1], '[A-Z]+it') = 0 AND regex_match([Field1], 'it[A-Z]+') = 0

This will exclude 'Credit', account for either uppercase or lowercase instances of 'IT', and works for the provided examples.

Hope this helps! 

[echuong1]

The IN function looks for the entire field to be equal to the specified string. See the below thread for more information on the difference between IN and CONTAINS:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Difference-between-In-function-and-Con...

Try using the CONTAINS function with spaces before and after IT. See the attached workflow. Let me know if that works!

[ups366]

Hi,@marlline141 

What output as your want ? like below pic:

[marlline141]

@ups366 

Yeah. I ended up going really messy like [Field1] equal to instead of contains because I am filtering job titles. If one observation is title, then it will be pulled. How crazy. 

Thank you very much for your help!!