community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Adding Multiple criteria to a Basic Filter

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.

 

Highlighted
Alteryx Certified Partner
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)
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!)

Meteoroid

This was a helpful approach. Thanks for posting!

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)

Alteryx Certified Partner
Alteryx Certified Partner
Is storage tier a string or numerical variable?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
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')

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")')

@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"))
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