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

[Nandit]

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]

Hi @Nandit ,

Can you try :

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

Let me know if that worked for you.

Cheers,

Angelos

[Jonathan-Sherman]

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]

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]

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 · Accepted solution]

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