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

Hi,

I am trying to create a table that will show me which vendors operate in two different markets one of them being Spain. Some of vendors will be listed more then once in the market thus making the pull function a bit difficult to use I believe.

Not sure where to go from here but I’ve been trying different codes with no luck.

Case when “Cases”.“VendorName” = “Cases”.“VendorName” AND WHEN “Cases”.“LocalMarket” = ‘Spain’ AND “Cases”.“LocalMarket” = ‘%’ THEN “Cases”.“VendorName” ELSE NULL END

Please let me know.
Anna

Hi Anna,

It’s 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 I’ll 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 it’s 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 :slight_smile: If it doesn’t 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 I’m trying to accomplish. Perhaps it is easier if i explain it with the attached diagram.

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 don’t you can use the following code.

  1. Dimension: Vendor name (usually “LFA1”.“NAME1”, if it isn’t 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 isn’t ‘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