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

Adding Multiple criteria to a Basic Filter

michael
6 - Meteoroid

I am having trouble setting up a Basic Filter where I would like to filter out 3 numbers from a field. I would only like to look at records that have those three numbers in a field.
The Basic Filter works fine when I enter one value but I get an error when I enter more than one.
I suspect that this would require a Custom Filter however, I am not sure how to set up the conditional expression.

Thank you in advance for your help.

 

13 REPLIES 13
kane_glendenning
10 - Fireball
Hi Michael,

The Custom Filter is needed. The basic filter for the number 1 would be:
[Field] == 1 
and the Custom filter for Numbers 1,3,5 would be:
[Field] IN (1,3,5)
benjamin_carley
6 - Meteoroid
Hello Michael!

In addition to using the filter tool, you can also use the join tool for this method. This makes it slightly easier to specify which values you want to filter on by being able to load them in an excel file. However it can slow down your workflow if you're filtering on a lot of values or you have a particularly large dataset.

For example, if I took my input and I had another file that listed my 'disallowed' values, if I joined my input on the left and my disallowed values on the right, the left output of the join tool would be the allowed values, the right output would be any unmatched disallowed values and the J output would be the values that disallowed values that were present in the input.

(Annoyingly I'm waiting for a refreshed license so everything is showing up with locks, but I hope this illustrates the point I was trying to make!)

tsnouffer
6 - Meteoroid

This was a helpful approach. Thanks for posting!

resqui
6 - Meteoroid

I am curious why I would receive a Parse Error at char (42): Parse Error using the customer expression......[StorageTier] IN (1,2,3,4)

MarqueeCrew
20 - Arcturus
20 - Arcturus
Is storage tier a string or numerical variable?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
ylau
5 - Atom

Add the Filter tool Use the below, it would need to be written using quotes

 

[Field name] in ('C', 'E', 'I', 'R', 'N', 'D', 'U')

mackenziek
6 - Meteoroid

Do you know why a parse error occurs for this IN function?

 


"Parse Error at char(63): Unknown function "IN"

I'm trying to filter for the last three days from today.  IE. I'd like to create one dataset that only contains a range of dates for the last three days.  Perhaps IN only works for single integers and not formulas.

 

[Date] = IN ('datetimeformat(DateTimeAdd(DateTimeToday(),-1,"days"),"%Y-%m-%d")','datetimeformat(DateTimeAdd(DateTimeToday(),-2,"days"),"%Y-%m-%d")', 'datetimeformat(DateTimeAdd(DateTimeToday(),-3,"days"),"%Y-%m-%d")')

patrick_digan
17 - Castor
17 - Castor

@mackenziek I would use a formula like:

 

DateTimeDiff(DateTimeToday(),[Date],"days") In (1,2,3)

For What It's worth, your formula would work if you take out the single quotes around your datetimeformats and remove the equal sign:

[Date] IN (datetimeformat(DateTimeAdd(DateTimeToday(),-1,"days"),"%Y-%m-%d"),datetimeformat(DateTimeAdd(DateTimeToday(),-2,"days"),"%Y-%m-%d"), datetimeformat(DateTimeAdd(DateTimeToday(),-3,"days"),"%Y-%m-%d"))
tchin007
5 - Atom

I just tried to do the IN function but it is giving me Parse Error.  Does the type of the sort filed need to be something that can have in?  I tried double, string, vstring and non of them is letting me use the function IN.

Labels