Hi,
I would like to merge this two filter togheter but I dont 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.
Page 1 / 1
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
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
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.