Applying multiple filters to various columns within the same data set

[Solonglolli]

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.)

 

[BrandonB]

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. 

[MarqueeCrew]

@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

[BrandonB]

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

[mbarone]

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).

[Solonglolli]

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?

[Solonglolli]

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

[Solonglolli]

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

[Solonglolli]

Thanks Mark! Simple and easy solution!

[Solonglolli]

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??)