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

[a.maciulewski]

Hi Calandra,

Everything was beginning to look great, when I uploaded one of the country files. However, when I combined the vendor list of the countries into one file. I received the below error:

Basically, there are occasions when 2 different local markets may want to exclude the same vendor. Thus we are getting the duplication error. Can you please let me know if there is something else I can do in case key matching that would solve this error, rather then having a separate file for each local market?

Thanks,
Anna

[c.eckert]

Hi Anna,

The problem is if you try to join the case table with a single excel sheet, which has the same vendor number in it several times is that means the tables have a many to many relationship. It’s not possible to join the tables under these circumstances as there is no distinct way the rows of the two tables relate to each other.

I tried to add another excel table that had just has the country names in to build a bridge between the case table and the excel sheet with the table names, but as it is then the parent table of both tables they still have a many to many relationship. After extensive testing I was unable to find a workaround to make the connection work.

One approach which I was able to make

The approach I tested which was able to cope with the same vendor numbers appearing for different countries was to use an excel sheet which has country name in one column and then in the other just list the vendor numbers excluded for that country the neighbouring cell in the format: '‘num1’,‘num2’,‘num3’… This format would mean you have a one to many relationship between case table and excel and could use the “IN” operator. The filter would look like this:

FILTER NOT(“table”.“country” = “excel”.“country collumn” AND ‘“table”.“vendor”’ IN (’“excel”.“vendor number list”’));

Please note an additional single comma is necessary in front of the first vendor in the list as I found one comma disappears on the way into celonis for some reason. Also make sure not to forget the single commas around “table”.“vendor” and “excel”.“vendor number list”, which are necessary to convert the type to string, otherwise the IN operator throws an error.

However, unfortunately excel sells can only hold at most 32,767 characters and I don’t think the south America list would fit.

I don’t think you can avoid using a separate excel sheet for countries with a lot of exclusions. But for countries with few enough exclusions you could use the approach described here to write them in one sheet to save time.

So you could have one shared excel and filter using the formula above and then to filter for the countries with so many exclusions they need there own sheet just use add the following filter for each country:

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

I hope this helps, I’m sorry I couldn’t find a perfect solution for you.

Best wishes,

Calandra