Chain of Basic filters to custom filter conversion

[ShantanuDagar]

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

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

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.

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