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