ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

Is there an alternative way of adding a blank along multiple filters for one column?

Nandit
5 - Atom

I am trying to filter PTC,REFD and blanks in the data set attached. However, when using [Business]IN("PTC","REFD","")  the filter is not picking up the blanks it only picks up PTC and REFD. Is there an alternative way to pick up the blanks in the business column?

AngelosPachis
15 - Aurora

Hi @Nandit ,

 

Can you try :

 

 

[Business]IN("PTC","REFD") OR ISEMPTY([Business])

 

 

Let me know if that worked for you.

 

Cheers,

 

Angelos

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @Nandit,

 

Are you able to attach some dummy data for us to test out with?

 

There are two approaches i'd potentially look into:

 

(1) Replacing "" with " " to check whether there's any trailing space

 

[Business]IN("PTC","REFD"," ")

 

 

 

(2) Perhaps taking a look into the IsEmpty() function

 

[Business]IN ("PTC","REFD")
OR IsEmpty([Business])

 

 

 

(3) Trimming the field before testing for ""

 

Trim([Business]) IN("PTC","REFD","")

 

 

 

Kind regards,

Jonathan

Nandit
5 - Atom

Hi Angelos, 

 

Thank you, however the ISEMPTY still doesn't pick up the empty cells in the Business column. Attached is the excel with the data if, the output i'm trying to achieve is in the correct output Tab. 

 

The filter I am currently inputting is: 
[Type ] = "Ext" AND
[Admin]IN("Peter","Joe ") AND
[Business ]IN("PTC","REFD") OR ISEMPTY([Business]) AND
[Amount ] != 0

 

Regards, 

 

Nandit

Nandit
5 - Atom

Hi Jonathan, 

 

Thank you, however the trying the below still doesn't pick up the empty cells in the Business column. Attached is the excel with the data if, the output I'm trying to achieve is in the correct output Tab. 

 

The filter I am currently inputting is: 
[Type ] = "Ext" AND
[Admin]IN("Peter","Joe ") AND
[Business ]IN("PTC","REFD") OR ISEMPTY([Business]) AND
[Amount ] != 0

 

Regards, 

 

Nandit

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @Nandit,

 

There's a few things you need to do here, firstly i've amended your filter condition to include brackets around the [Business ]IN("PTC","REFD") OR IsNull([Business ]) section. Otherwise Alteryx will treat the first condition as seperate to the second, with the brackets Alteryx will look for one of those two conditions as the "AND" statement.

 

[Type ] = "Ext" AND
[Admin]IN("Peter","Joe") AND
([Business ]IN("PTC","REFD") OR IsNull([Business ])) AND
[Amount ] != 0

 

Secondly, i've used a data cleansing tool to convert null values in your [Amount] field to be zero, which will allow your last condition to be satisfied (Amount != 0).

 

Thirdly, i've removed leading and trailing spaces in string fields (again with the data cleansing tool) to avoid any mis-matches.

 

I've attached my workflow for you to download if needed.

 

Kind regards,

Jonathan

 

Labels