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

Filtering on Multiple Fields for Zeros

trailfarley
6 - Meteoroid

I have a dataset where I need to filter out lines of data that contains 0 in two fields. The current filter I have is:

 

[AP Amount] != 0 and [Expense Amount] != 0

 

Unfortunately this filter removes all lines of data that have a zero. I'm looking to keep lines of data that may have a 0 in one field and a value in the other. Can anyone assist?

7 REPLIES 7
Emil_Kos
17 - Castor
17 - Castor

Hi @trailfarley,

 

I think you need to use this formula:

 

[AP Amount] = 0 and [Expense Amount] = 0

 

And connect the data to the false anchor of the filter tool. 

 

Don't ask me why you need to do it like that but trust me it will work 😛 

trailfarley
6 - Meteoroid

sorry I may not be following what to do to the false anchor. Could you elaborate?

Luke_C
17 - Castor

Alternatively to @Emil_Kos's solution, change the AND in your existing filter to be OR. It's a bit counterintuitive but I think it's a product  of using the not equal to operator.

Emil_Kos
17 - Castor
17 - Castor

Hi @trailfarley,

 

I have prepared a workflow for you:

Emil_Kos_0-1623246092615.png

 

Emil_Kos
17 - Castor
17 - Castor

Hi @Luke_C,

 

Actually, your way is better as this makes more sense to connect to true anchor. 

danilang
19 - Altair
19 - Altair

Another way to set it up is to use 

!([AP Amount]=0 and [Expense Amount]=0)

which takes @Emil_Kos logic and negates it to send it out the true anchor.  The two statements

!([AP Amount]=0 and [Expense Amount]=0)

[AP Amount]!=0 OR [Expense Amount]!=0

are logically equivalent

 

Dan

Emil_Kos
17 - Castor
17 - Castor

Hi @danilang,

 

Very elegant and very simple: 

!([AP Amount]=0 and [Expense Amount]=0)

I will keep that in mind in the future as I always build it another way around. 

 

Labels