Chain of Basic filters to custom filter conversion

Hey all,

I created a chain of filter and union in a workflow for validation and matching the correct and incorrect values based on a decision tree.

The final output which I am getting right now is perfect.

I got the feedback to club multiple basic filters in Custom filter using AND operators etc.

But having a lot of confusion in False values and even true values in such cases.

Attached below is the sample workflow on paper.

Can someone help with the same for getting same output? And maybe explain what the false values in your respective custom filters will provide

doubt.jpg

Workflow

Help

Accepted answers

Christina_H

There are three possible outcomes for your data, one of the two unions or records that aren't included in either. You need at least two basic filters to separate them.

I've converted your filters to a formula assigning the values as True / False / Ignore for the three outcomes. It can probably be simplified but gives the same outcomes as the series of filters. Hopefully I've been able to read your writing correctly!

IF ([Country]="Japan" AND [RMD]=500 AND [Status]="Yes")
OR ([Country]="Japan" AND [RMD]!=500 AND [Company]="Registered" AND [XNA]=2123 AND [Status]="Yes")
OR ([Country]="Japan" AND [RMD]!=500 AND [Company]="Registered" AND [XNA]!=2123 AND [Status]="No")
OR ([Country]="Japan" AND [RMD]!=500 AND [Company]="Unregistered" AND [Status]="No") THEN "True"
ELSEIF ([Country]="Japan" AND [RMD]=500 AND [Status]!="Yes")
OR ([Country]="Japan" AND [RMD]!=500 AND [Company]="Registered" AND [XNA]=2123 AND [Status]!="Yes")
OR ([Country]="Japan" AND [RMD]!=500 AND [Company]="Registered" AND [XNA]!=2123 AND [Status]!="No")
OR ([Country]="Japan" AND [RMD]!=500 AND [Company]="Unregistered" AND [Status]!="No") THEN "False"
ELSE "Ignore"
ENDIF

Attached workflow contains sample data and the original set of filters that I recreated in case anyone else wants to use them.

Filtering.yxmd

All comments

Emmanuel_G

Hello @ShantanuDagar

Possible to share even a dummy sample of your dataset to test ?

ShantanuDagar

A bit difficult to share it.

Christina_H

There are three possible outcomes for your data, one of the two unions or records that aren't included in either. You need at least two basic filters to separate them.

Attached workflow contains sample data and the original set of filters that I recreated in case anyone else wants to use them.

Filtering.yxmd

ShantanuDagar

Champ, it's working perfect. Thanks

ShantanuDagar

@Christina_H Hey.

I have a column with the names of the companies and I have to include all the names with the word "LLC" in that "company name" along with AND operator in the codes.

What code expression should I enter?

I am using Contains([Company name], "LLC") and !Contains([Company name], "LLC") but it's not working

Christina_H

@ShantanuDagar

Contains([Company name],"LLC") is the right expression, but you'll need to get it in the right place in the formula to do what you want.

I assume the "and" in your sentence isn't an operator? Because the ! before the statement means NOT, and the company name can't both contain LLC and not contain LLC.

ShantanuDagar

Both expressions are not in 1 line.

1st expression is in 1 line with some other filters like country etc. separated by ANDs just like you provided.

Then there is an OR operator in between 2 lines and then the second expression in that line with similar other filters separated by AND.

Just to filter based on if they contain the word "LLC" or do not contain it.

But in the final result, it doesn't get filtered out on the basis of containing or not containing.

Moreover, C in the expression "LLC" after running the code changes to C with a small dot or line below it. (Don't know if the issue is with that)

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703