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