Creating Table that includes vendors that operate in at least 2 distinct markets

Hi Anna,

Its not clear to me how the column Cases.LocalMarket is structured. It seems likely to me that it contains a list of country names, separated by some kind of symbol like a comma.

The solution Ill present here assumes the values look like this like Spain,Germany,USA,

CASE WHEN Cases.LocalMarket LIKE %,Spain% OR

Cases.LocalMarket LIKE %Spain,%

THEN "Cases.VendorName ELSE NULL END

To explain: for Spain not to be the only country then another country must be listed before or after it, that means the separator symbol must come immediately before or after it. Two conditions using LIKE are therefore needed one with the separator before Spain and once after it. If its in the middle of the list, then both are true which is fine. The % in the like condition can stand in your any number of characters.

If your column uses a different separator (e.g. a semicolon) just swap the commas in my code for that symbol. Another potential issue is that the code assumes that there are no spaces ( ') between the country names. If there are just use %, Spain% instead.

I hope this solves your problem

If it doesnt please reply with a screenshot of a table containing Cases.LocalMarket so that I can have a look at the structure.

Best wishes,

Calandra

Hi Calandra,

I think I was not very clear as to what Im trying to accomplish. Perhaps it is easier if i explain it with the attached diagram.

Venn Diagram.JPG812478 39.9 KB

Basically, each invoice will have a vendor and a local market. I would like to know which vendors intersect. i.e. which vendors have invoices that sit with Spain, plus any other local market.

Please let me know if this is more clear.

Cheers,

Anna

Hi Anna,

Thank you, I think I now understand what your trying to achieve.

To make a table that shows for all vendor names which ones has invoices from Spain and any other market and which dont you can use the following code.

1. Dimension: Vendor name (usually LFA1.NAME1, if it isnt please swap this column out for the correct one in the rest of the solution)
2. KPI:

CASE WHEN PU_COUNT(DOMAIN_TABLE(LFA1.NAME1), Cases."_CASE_KEY", Cases.LocalMarket != Spain) > 0 AND

PU_COUNT(DOMAIN_TABLE(LFA1.NAME1), Cases."_CASE_KEY", Cases.LocalMarket = Spain) > 0

THEN 1

ELSE 0

END

To explain: PU_COUNT(DOMAIN_TABLE(LFA1.NAME1), Cases."_CASE_KEY", Cases.LocalMarket != Spain) counts for each vendor name how many cases its associated with that has a market that isnt Spain. The case when statement returns a 1 for vendors that have at least one invoice from Spain and from another market.

If you just want a list of vendors which are in Spain and another market and to hide all others you can do this by making an OLAP table with Vendor name as a dimension and a slight variant on the above PQL code as a component filter:

FILTER ISNULL(CASE WHEN PU_COUNT(DOMAIN_TABLE(LFA1.NAME1), Cases."_CASE_KEY", Cases.LocalMarket != Spain) > 0 AND

PU_COUNT(DOMAIN_TABLE(LFA1.NAME1), Cases."_CASE_KEY", Cases.LocalMarket = Spain) > 0

THEN 1

ELSE NULL

END) = 0;

Does this now solve your problem?

Best wishes,

Calandra

Hi Calandra,

I have gone ahead and put in the 2nd option and received the below error message. Can you please let me know what I have done incorrectly?

Cheers,

Anna

Celonis Error 3.JPG867716 49.6 KB