Load Script - using a case when statement

[a.maciulewski]

Hi All,
I am looking to build a case when statement in the load script area. Basically, I am building an analysis where certain local markets want to exclude fields . For example, for the Germany local market, we would like to exclude all FI vendors starting with ‘3’ and in Spain they want exclude all real estate invoices, and so on and so on.

As these filters will apply to the entire spreadsheet, I was hoping to write the code into the load script area, for ease of use, instead of each olap table but I am getting an error. Is this because you cannot write case when statement in the load script setting? And if not, can you please propose a workaround?

Thank you,
Anna

[d.becher]

Hi Anna,

I think you don’t need a CASE WHEN, you can simply write your condition directly inside the FILTER statement.
It would look something like this:

FILTER "table"."country" = 'Germany' and "table"."vendor" NOT LIKE '3%' 
OR  "table"."country" = 'Spain' and "table"."vendor" NOT LIKE ... ;

Best
David

[a.maciulewski]

Hi David,

Thanks for your response, however this does not work for me. I need the data set to return all of the other market information that does not have these restrictions. For example, when using the code

FILTER “CASES”.“LocalMarket” = ‘Germany’ and “CASES”.“VENDORNUMBER” NOT LIKE ‘3%’;

The data set now only brings back cases for Germany, but excludes all cases for Albania, Portugal etc. where the restrictions do not apply. This is why I thought I needed the CASE WHEN function.

Please let me know.

Cheers,
Anna

[d.becher]

Hi Anna,

in the FILTER statements, you don’t specify what should be excluded, but instead, you specify what should be displayed.

So additionally, you need to specify that if you have a country without any restrictions, you want to keep those as well.
So I’d add another OR case:

FILTER (“CASES”.“LocalMarket” = ‘Germany’ and “CASES”.“VENDORNUMBER” NOT LIKE ‘3%’ )
OR “CASES”.“LocalMarket” NOT IN ('Germany')

In the NOT IN clause, you need to specify all countries where a restriction applies.

Best
David

[a.maciulewski]

Thanks David, I will play around with the code. Also, can you tell me if I can use the variable function in my code such as:

FILTER “CASES”.“LocalMarket” = ‘Germany’ and “CASES”.“VENDORNUMBER” NOT LIKE ‘03%’ OR “CASES”.“LocalMarket” NOT IN <%=Local_Market%>

I am getting a something is wrong error message.

Cheers,
Anna

[d.becher]

Hi Anna,

you need to put brackets around the variable, since brackets are required by the IN operator:

FILTER “CASES”.“LocalMarket” = ‘Germany’ and “CASES”.“VENDORNUMBER” NOT LIKE ‘03%’ OR “CASES”.“LocalMarket” NOT IN (<%=Local_Market%>)

The variable itself should be of the Text/Replacement type, and the content should look like this:
'Germany', 'Spain', ...

Cheers
David

[a.maciulewski]

Hi David,

Still not working.

I have this in the Load Script:
FILTER “CASES”.“LocalMarket” = ‘Germany’ and “CASES”.“VENDORNUMBER” NOT LIKE ‘03%’ OR “CASES”.“LocalMarket” NOT IN (<%=Local_Market%>)

And the Text/replacement variable as :
‘Spain’, ‘Portugal’

[d.becher]

In the variable, you need to specify all countries which are special cases, in your example, it would be ‘Germany’. This should work then.

Cheers
David

[a.maciulewski]

Hi David,

I added ‘Germany’ in the variable and its still not working. Screenshots below.

Btw. The code works when I’m not using the last statement.

[d.becher]

What do you mean by “It’s not working”? Do you get an error message?

[a.maciulewski]

Hi David,

Sorry, I didn’t see your latest response.

Yes, I get the attached error message.

[a.maciulewski]

Hi David,

Also, As I am extending this logic to exclude more variables, I’m not sure if this is the correct way of going about this.

For example, for the German local market, i also need to exclude the real estate invoices for vendors that start with ‘8’.
Using the not like function, as we talked about before, excludes both vendors starting with ‘8’ and all real estate invoices. this is why i believe i need to use the case when function.
Please advise.

Cheers,
Anna

[c.eckert]

Hi Anna,

It is possible to use a case when statement in a filter. Its less efficient than using multiple filters which is why David isn’t keen on it. If its the solution your most comfortable with then that’s fine to do it that way.

While this code is perhaps not the most elegant or compact solution but it’s probably the most user friendly. The idea is to use a case when statement, where you just list all combinations you want to exclude in separate WHEN statements and just assigning all of them the value 0. Finally assign all cases did not affect my any of these conditions the value 1. The filter then only allows those cases for which the case when statement gives a 1.

Here is a template for the PQL code. Just add you conditions in this form.

FILTER (CASE
WHEN “table”.“country” = ‘Germany’ AND “table”.“vendor” LIKE ‘3%’ THEN 0
WHEN “table”.“country” = ‘Spain’ AND “table”.“type of invoices” = ‘real estate’ THEN 0
WHEN … THEN 0
…
ELSE 1
END) = 1;

It should be fine to use variables in these conditions.

Like I said this isn ‘t a very computationally efficient way of filtering, as each case needs to be checked against each condition. But if its it takes so long to load that it bothers you then you can have a go back to trying to make David’s solution work but if you don’t have huge amounts of data this should solve your problem.

Best wishes,

Calandra

[a.maciulewski]

FILTER (CASE
WHEN “table”.“country” = ‘Germany’ AND “table”.“vendor” LIKE ‘3%’ THEN 0
WHEN “table”.“country” = ‘Spain’ AND “table”.“type of invoices” = ‘real estate’ THEN 0
WHEN … THEN 0
…
ELSE 1
END) = 1;

Hi Calandra,
I received the below error: Can you please give me a ring to discuss. +44 (0) 7825 756905

[c.eckert]

Hi Anna,

I just sent you an email to invite you to a Zoom meeting with me so we can use screen share to resolve the issue.

Best wishes,
Calandra

[c.eckert]

Just in case anyone else with a similar problem finds this post, here is the solution that we ended up with:

List the conditions for exclusion as a series of filters, using ‘NOT’ to swap true and false around so that cases are excluded for which the condition inside the brackets is true.

So for Anna’s problem the code was:

FILTER NOT(“table”.“country” = ‘Germany’ AND “table”.“vendor” LIKE ‘3%’);
FILTER NOT(“table”.“country” = ‘Spain’ AND “table”.“type of invoices” = ‘real estate’);
etc. …

Please be aware that when copying code from the community into Celonis problems can arise due to the way the community formats commas. In Celonis the commas must be straight, it is therefore often necessary to delete the commas in the copied code and type in new ones once inside Celonis.

Best wishes,

Calandra

[a.maciulewski]

Hi Calandra, Hope you are doing well.

I am continuing to build out the analysis we have resolved earlier and as other local markets come back with their own list of exclusions, I am facing new challenges.

For example, for local market South Africa, they have a list of 6,000 vendors they would like to exclude. I am not sure if it makes sense to list out the vendors in the load script area. (I really would not like to go down that route). I have also tried to use the variable function, where I can include the list as a variable and then have the load function area reference that. Also, I am not sure if that makes a lot of sense for 6000 vendors. (Also, I have tried it with 3 vendors, and I am getting an error message. But that’s probably because im doing something incorrectly on my end)

Can you please let me know if you have some thoughts as how best I can achieve this.

Perhaps building a table in the back end? And then reference that table?

Thanks,
Anna

[c.eckert]

Hi Anna,

You could make an excel sheet with all the vendors you wish to exclude.

In the excel have a column with the vendor number (or if that’s not available vendor name), another that says the country for which it should be excluded (the name should match the way it’s written in “Cases”.“LocalMarket”) and another column called something like “excluded” where every row in the table has the value ‘yes’.

The next step is to integrate this into your data model. (Please not this explanation is your Celonis 4 customers, for any IBC customers reading this, excel sheets are uploaded by opening the relevant “data pool” in the “Event collector” and going to “File Uploads” and uploading the file there.)

For a Celonis 4 customer like you, go to the Celonis start page, click on the relevant project, then on “data models” and select the data model used in the relevant analysis.

Then in the data model, click on the “tables” tab, then on “Import data”, finally select “Import from file” from the drop down.

In the next window add your file.

The next step is to connect this table to the vendors table of your database. Do this by going to the keys tab and clicking add foreign key. Then for table1 select the excel you just uploaded and for table2 your Vendor table. Then select then from both tables select the Vendor number.

Then when you open your analysis, all tables will be loaded including your excel. So, you can now add a filter

FILTER NOT(“table”.”country” = “South Africa” AND “excel table”.”exclude” = ‘yes’);

I haven’t been able to fully test this solution so if it doesn’t work please let me know.

Best wishes,

Calandra