Combiner deux filtres Combine two filters

Hi,

I would like to merge this two filter togheter but I don’t found the right formula :

FILTER “EKKO”.“BSART” IN (‘ZNB’ , ‘Z3P’, ‘ZTR’, ‘ZRS’) and FILTER “EKKO”.“EKGRP” ***!= (‘995’)
with
FILTER “EKKO”.“BSART” IN ( ‘ZST’ , ‘Z3P’, ‘ZTR’, ‘ZRS’) and FILTER “EKKO”.“EKGRP” = ‘995’ ;

Thanks in advance.

Hi @Audrey,

thanks for reaching out!

Generally, you can apply two filters which are independent from each other by simply stating the two filters after each other, with a semicolon after each FILTER e.g.:

FILTER XYZ1;
FILTER ZYZ2;

-> both filters will be applied.

However in your example, the first filter is using EKKO.EKGRP != ‘995’ and the second one EKKO.EKGRP = ‘995’. If we would simply list the two filter statements after each other, we would end up with an empty table.

Thus, we have to use a CASE WHEN construct as the following:

FILTER
(
CASE WHEN
(“EKKO”.“BSART” IN (‘ZNB’ , ‘Z3P’, ‘ZTR’, ‘ZRS’) AND “EKKO”.“EKGRP” != ‘995’)
THEN 1.0
WHEN
(“EKKO”.“BSART” IN (‘ZST’ , ‘Z3P’, ‘ZTR’, ‘ZRS’) AND “EKKO”.“EKGRP” = ‘995’)
THEN 1.0
ELSE 0.0
END
) = 1.0

This way, the FILTER statement will filter for all cases for which either one of the two constraints (followed by THEN 1.0) applies.

Feel free to copy the statement above into your analysis.
Please be aware that you might have delete and add the " quotation marks as well as the ’ single quotation marks as they might be in a wrong format when copying PQL code from the community into Celonis.

Please let me know if you have any further questions or if this topic is resolved successfully.

Best regards,

Your Celonis Data Science Team