Joining selection criteria for filtering with PQL

Hi,

does anybody know a way of implementing a filter in PQL similar to this SQL query?

select * from “TableScheme”.“Table B” Case_Tab
where itemcaseid in
(select itemcaseid
from “TableScheme”.“Table A” Activity_Tab
where
activity = ‘Activity A’
and flag >= 1
)
and itemcaseid in
(select itemcaseid
from “TableScheme”.“Table A” Activity_Tab
where
activity = ‘Activity B’
and flag = 0
)

I want to extract all itemcaseids which satisfy both conditions and I cannot find out, how to apply multiple filter conditions using the selection tool in Celonis.

Thanks and kind regards,

Konstantin

Hi Konstantin,

Thanks for reaching out to us and sorry for the late reply! Could you please share with us how the tables A & B are connected within your data model?
And just to be sure, do you want to implement this within a filter, such as a component or sheet filter? Or should it be implemented within a selection as in the end you are talking about the selection tool in Celonis?
These additional information would help a lot to further assist you. Thanks in advance!

Hi Markus,

A and B are connected via itemcaseid. How it is implemented does not matter, filters and selections are both fine.

Thanks for your help,

Konstantin

Hi Konstantin,

thanks for the fast reply! If I understand you correctly, then there are two conditions that should be applied to select itemcaseids, both again consisting of two conditions each. First, IDs should equal ‘Activity A’ AND flag >= 1. Second, IDs should equal ‘Activity B’ AND flag = 0.
This logic can for example be implemented in a component filter in the following way:

FILTER “TableScheme”.“Table A”.“Activity” = ‘Activity A’ AND “TableScheme”.“Table A”.“flag” >=1;
FILTER “TableScheme”.“Table A”.“Activity” = ‘Activity B’ AND “TableScheme”.“Table A”.“flag” =0;

The solution is to use two FILTER statements, each specifying the corresponding filter conditions. This did work on my local data. Please try whether this solution works for you. In case you still encounter any issues, please get back to us!

Hi Marcus,

this is the solution I tried first, which did not work out. It shows me - out of XX Cases. Is Celonis able to somehow chain two different filter cases with PQL like in my example?

Best and thanks,

Konstantin

Hi Konstantin,

could you please check each of the filter statements individually? This is to infer whether each statement does work for itself and generates a subset of cases. Further, do you use the filter on a component or as a sheet filter? And last, if you use the filter on a component, does this component show an error message, or does it simply exclude all cases?

These information would help a lot to refine the filter statement. Thanks in advance!

Hi Marcus,

both filters individually work, I tried component and sheet filter. When used as a component filter, the OLAP table I want to view the items in is just empty. When using it as a sheet filter, the OLAP table is also empty and it shows 0 out of XX cases, so this means it excludes ALL cases? The “- out of XX cases” was just a typo in the sheet filter.

Best,

Konstantni

Hey Konstantin,

this does in fact sound like the filters should be working correctly. If the component is empty or if it shows 0 out of XX cases, then this means that all cases are excluded. Meaning that none of the cases does in fact fulfill both criteria. You can test this by “relaxing” some of the conditions and see which condition cancels out all of the cases.

Are you sure that some cases do meet all of the conditions? Please bear in mind that in contrast to the SQL statement, within Celonis you are working on a case-level, meaning that a case needs to fulfill all of the conditions in order to be shown.

Hi Markus,

see this example:

id activity flag
1 alpha 1
2 beta 0
3 alpha 1
4 beta 0
5 alpha 0
6 beta 1

I would want to filter activity = alpha & flag = 1 as well as activity = beta and flag = 0, so finally it would show me id 1, 2, 3 and 4.

When I upload the data in Celonis and apply a filter, it leads to showing me an empty OLAP table and 0 out of 6 cases selected.

Best,

Konstantin

Hi Konstantin,

when applying multiple FILTER statements, they are connected with an AND, which means that both conditions have to be fulfilled.
What you need, however, is an OR. The logic would be:

(alpha AND 1) OR (beta AND 0)

We can write that in the FILTER statement:
FILTER ("Table"."Activity"='alpha' AND "Table"."Flag"=1) OR ("Table"."Activity"='beta' AND "Table"."Flag"=0);

Cheers
David

Aaah makes sense that way :slight_smile: Thank you very much!