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

Alteryx designer Discussions

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

Custom Filter with AND and OR conditions

Atom

Hi All,

 

Absolute beginner here, so please be gentle :)

I am wondering why a custom filter I have built is not returning the expected results - in this example the source is a simple excel file with 282 rows.

I am filtering on two columns within the sheet, "IS VALID" and "CAUSE".

245 Rows in the source file have a value of "VALID", 37 are "INVALID"

The "CAUSE" column is populated on all rows and has 9 distinct values - 01 through to 09

I want the filter to remove all "INVALID" rows and only return rows with a cause equal to 1,2,3 or 4...

122 rows have a cause equal to 1,2,3 or 4, of these rows, 111 have an "IS VALID" value of "VALID" - so in the output of the filter I would expect to see 111 rows - but instead I am seeing all 122 rows - This is the filter I thought should work:

 

[IS VALID]="VALID"

AND

[CAUSE]="1"

OR

[CAUSE]="2"

OR

[CAUSE]="3"

OR

[CAUSE]="4"

 

I have worked around it by splitting the two filters - first on "CAUSE" and then a second on the true results of that filter to filter on "IS VALID" - these return the correct results - 111 Valid rows with a Cause of 1,2,3 or 4.

So two questions:
1: Am I misunderstanding the functionality of the "AND" operator?

2: Is there a simpler way to include multiple "OR" values - eg [CAUSE] INLIST "1","2","3","4"]

 

Appreciate any help you may have,

Cheers,

 

Sko.

Atom

Ok - Fixed the first issue - forgot the brackets :P

[IS VALID]="VALID"

AND

([CAUSE]="1"

OR

[CAUSE]="2"

OR

[CAUSE]="3"

OR

[CAUSE]="4")

 

But is there a better way to phrase the "OR" section?

 

Cheers,

 

Sko.

Highlighted
Asteroid

Hi @Skoville ,

 

You can also phrase your multiple OR conditions in below way.

 

[IS VALID]="VALID" AND  [CAUSE] IN ("1","2","3","4")

 

Thanks,

Sumit

Labels