Hi Anna,
I think you dont 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
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
Hi Anna,
in the FILTER statements, you dont 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 Id 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
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
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
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
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
Hi David,
I added Germany in the variable and its still not working. Screenshots below.
Btw. The code works when Im not using the last statement.
Script.JPG1344204 22.3 KB
What do you mean by Its not working? Do you get an error message?
Hi David,
Sorry, I didnt see your latest response.
Yes, I get the attached error message.
Celonis error.JPG1356413 19.8 KB
Hi David,
Also, As I am extending this logic to exclude more variables, Im 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
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 isnt keen on it. If its the solution your most comfortable with then thats fine to do it that way.
While this code is perhaps not the most elegant or compact solution but its 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 Davids solution work but if you dont have huge amounts of data this should solve your problem.
Best wishes,
Calandra
c.eckert:
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
celonis error.2.JPG1329135 17.4 KB
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
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 Annas 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
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 thats 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
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 thats not available vendor name), another that says the country for which it should be excluded (the name should match the way its 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.
For Anna's post 1.png1563567 35.5 KB
Then in the data model, click on the tables tab, then on Import data, finally select Import from file from the drop down.
For Anna's post 2.png1909484 56.4 KB
In the next window add your file.
For Anna's post 3.png1895620 74.2 KB
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 havent been able to fully test this solution so if it doesnt work please let me know.
Best wishes,
Calandra
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:
Erro - Celonis- JPG.JPG926250 23.5 KB
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
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. Its 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 dont think the south America list would fit.
I dont 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, Im sorry I couldnt find a perfect solution for you.
Best wishes,
Calandra
Hi Calandra,
Thanks for helping us out here. Really appreciate your effort.
I have discussed the various solutions with my team, and an idea came up. I am not sure if it will be feasible, but would it be possible for us to concatenate the excel file so that local market and vendor id is unique per row in our uploaded file, and then somehow either add another column in the table that we are matching to with the concatenated info, or write in a function to match based on those 2 criteria.
Please let me know and thanks again!
Cheers,
Anna
Hi Anna,
Thats a good idea. It should work provided the Vendor ID and the local market are in the same table. As far as I can remember they are both in your case table so that should be fine. In Celonis you can concatenate columns using ||. The following filter ought to do the trick:
FILTER NOT(table.country || table.vendor = excel.column with Local Market and Vendor ID concatenated);
Best wishes,
Calandra
