Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Applying multiple filters to various columns within the same data set

Highlighted
7 - Meteor

Hi Gurus,

Hope you can help me with the following:

I have a data set with several dimensions. I need to manipulate the data by applying multiple filters. All the filters that are applicable are either to exclude or include specific values of the records o the dataset. Here is a list of the filters that I need to apply:

 

1. 'SOURCE CODE' column: exclude all records with value 'GBFGWWSS01'

2. 'BRANCH CODE' column: include only those records with value '202'

3. 'NETWORK' column: include only those records with value 'SWIFT'

4. 'BENEFICIARY INST2' column: exclude all records with the following values:

’PNBPIE2DXXX’ 

‘PNBPGB2DXXX’

‘PNBPDEFFXXX’ 

‘WFBIUS6SXXX‘

‘WFBIUS6AXXX’ 

‘PNBPGB2WSIL’

 

So again, the columns are within the same data set, and the filters must be applied in this order. I have attached the formula tool settings I have applied but something doesn't add up as when I try to calculate sum of values after all the filtering is done, I'm not getting the same figures when comparing it to the pivot table results in the original excel file.

(The other tools in my workflow (including the summarize tool) are correct as I have configured them based on the recommendation of an Alteryx Community Guru, who helped me to set up the calculation part of the workflow.)

filters.JPG

 

 

 

 

Highlighted
Alteryx
Alteryx

Your logic looks correct, but I would probably take a close look at the contains formulas to make sure that these are pulling the data as you would expect. Also, the other thing to confirm would be whether or not the case of the characters are all uppercase. This would throw off the [SOURCE CODE] != GBFGWWSS01 for example. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@Solonglolli ,

 

using multiple filters makes it easier to read and understand your workflow. What's more, it performs faster if the filters are out in order of biggest impact first. 

Here's a complete formula:  

 

[SOURCE CODE] = 'GBFGWWSS01' AND

[BRANCH CODE] = '202' AND

[NETWORK] = 'SWIFT' AND

[BENEFICIARY INST2] NOT IN

(

’PNBPIE2DXXX’ ,

‘PNBPGB2DXXX’,

‘PNBPDEFFXXX’ ,

‘WFBIUS6SXXX‘,

‘WFBIUS6AXXX’ ,

‘PNBPGB2WSIL’)

 

 cheers,

 

mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx
Alteryx

Splitting these into separate filter tools might also be helpful so you can see what is being kept or filtered out for each formula.

Highlighted
13 - Pulsar
13 - Pulsar

The AND operator means that you're filtering for each and every one of those criteria to be true at the same time.

 

I suggest inserting a formula tool before the filter, and making the formula something like:

NAME OF NEW FIELD:  KEEP

FORMULA OF NEW FIELD:
IIF([SOURCE CODE]='GBFGWWSS01','N',

IIF([BRANCH CODE]='202','Y',
IIF([NETWORK]='SWIFT','Y',

.

.

.

))))))))

 

This will evaluate each line one by one and a


And then in the filter tool after the formula tool, filter for KEEP='Y'

Note that I prefer the Boolean IIF as opposed to doing "IF THEN ELSE".  For no other reason than it's less typing (no "ELSE" statements needed).

Highlighted
7 - Meteor

Hi Brandon,

Thanks for your response.

 1. Contain formula: I probably should have used the 'equals to' formula (i.e.[BRANCH_CODE]= "202") instead, as my intention with the filter was to include only fields that equal to number 202 (and exclude all other fields with number 203/204).

2. Please clarify what you mean by 'throw off the [SOURCE CODE] != GBFGWWSS01'? Isn't all formulas case sensitive? In the example I provided all the column names and record values are reflected as they appear in the dataset, meaning they are in upper case where they appear upper case in the data set. Did you mean that for certain formulas we shouldn't write the attributes in a case sensitive way as the formula is only able to read lower case?

Highlighted
7 - Meteor

Thanks for the interesting approach, I never thought of adding a new column!

Highlighted
7 - Meteor

Also the Boolean IIF is much more efficient, thanks for that, I'll be using it from now on.

7 - Meteor

Thanks Mark! Simple and easy solution!

 

 

Highlighted
7 - Meteor

Hi Brandon,

 

You are suggesting to add separate Formula tools for each filter function? If so should I arrange the individual formulas in a linear fashion, meaning that the output of one formula tool would be the input of the next formula, or should i apply them parallel to each other and then unite the output of each formula tool using the union tool?(I'm actually not sure if the second option would work, as the formulas may override one another wouldn't they??)

Labels