Conditional Filter


#1

Hello,

I would like to know if it is possible to apply a filter just in certain cases. For example, I tried to use the following code, but I get an error:

CASE WHEN ‘Condition’ THEN FILTER… END

Could you tell me how should I write a conditional filter, please?
Thank you in advance.

Best regards,
Catarina


#3

Hi,

Make condition part of the filter. I.e. if you want to apply size filter only to the red apples write
FILTER “apples”.“color” != ‘red’ OR “apples”.“size” > 50;


#4

Hi,

Thank you for your answer, but I think this does not apply in my case.
What I intend to do is to apply a filter only if something happens. For example, we have a variable that can be empty and I just want to apply the filter when this variable is not empty.

I have tried several things, such as:
CASE WHEN VARIABLE != ‘’ THEN FILTER … END

Could you tell me how should I write this filter, please?
Thank you in advance.

Best regards,
Catarina


#5

Hi,

As i understood, You can try with this FILTER ISNULL(Variable)=0, it will hide all the empty value then you can apply your filter with the Variable that you want to look for i.e for your Example CASE WHEN VARIABLE ‘’ THEN …ELSE NULL END

With Regards

Rahul


#6

Hi Catarina and Rathul,

Just wanted to add that evaluating a variable with the ISNULL() function has never worked for me (as you’ve probably already discovered). I believe the reason is ISNULL() expects a column, and therefore cannot be used to evaluate a single value such as a variable. It would be great if there was some other function that we could use to determine whether a variable is null, but unfortunately I haven’t come across a way to handle null variables yet.

-Tyler


#7

Hi Tyler, hi Rahul

Thank you both for your answers.

A variable can’t be null and that’s why the function ISNULL() doesn’t work with variables. A variable can just be empty. So, if we are working with variables we have to write:
’VARIABLE’ = ''

Maybe I’m not explaining myself well, but that’s not what I mean, Rahul. What I intend to do is apply a filter only if something happens. For example, I want to apply a filter to the CASES.CASE_ID column only if my variable is not empty.
Something like:
CASE WHEN ‘VARIABLE’ != ‘’ THEN CASES.CASE_ID = ‘…’ END
I do not know if Celonis has this functionality implemented. If I can solve this problem, I’ll put the solution here.

Best regards,
Catarina Amaral


#8

Hi Catarina,

You can not use condition in filter. CASE WHEN ‘VARIABLE’ != ‘’ THEN CASES.CASE_ID = ‘…’ END would not work

However you can achieve exactly the same result if you use following filter
FILTER <%= VARIABLE %> = ‘’ OR CASES.CASE_ID = ‘…’ ;

If your variable is empty first condition in filter will be TRUE and even if second condition is FALSE will see all cases. If your variable is not null, you will need second condition to be true for line to appear, so will see only cases matching the condition.