-2

I’m trying to categorize a custom field like this:

CASE
  WHEN REGEXP_MATCH(Campaign, "BLU") THEN "Colour Blue"
  WHEN REGEXP_MATCH(Campaign, "GRE") THEN "Colour Green" 
  WHEN REGEXP_MATCH(Campaign, "CAR") THEN "Product Car"
  WHEN REGEXP_MATCH(Campaign, "MOT") THEN "Product Motorbike"
END

It works fine for campaigns with only one “tag”, like MOT or GRE, for example.

I have also campaigns with more than one tag, for example CAR+GRE, and the REGEXP only returns the first true condition, in this case, CAR. But I’m not able to categorize both, CAR and GRE.

logi-kal
  • 7,107
  • 6
  • 31
  • 43
brjoan
  • 11
  • 3
  • Could you elaborate by providing a publicly editable Google Data Studio Report (additionally, a Google Sheet if it's the data set) of the scenario (using sample data that shows 1) Input values (~10 rows) 2) Expected output 3) An attempt at solving the issue)? It would help users visualise the issue and test out suggestions on a specific use case with objective right / wrong answers. Without a [Minimal Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) it would be difficult to pinpoint a suggestion and the issue, e.g. Data Set, Data Source, Report, Fields, Chart – Nimantha Feb 15 '22 at 04:07

1 Answers1

1

Wouldn't it be more sensible to have two dimensions? One for colour and one for product?

You could then do something like:

For colour dimension:

… 
WHEN REGEXP_MATCH(Campaign, ".*BLU") THEN "Blue"
WHEN REGEXP_MATCH(Campaign, ".*GRE") THEN "Green"

For Product dimension:

...
WHEN REGEXP_MATCH(Campaign, "CAR.*") THEN "Car"
WHEN REGEXP_MATCH(Campaign, "MOT.*") THEN "Motorbike"

…

Bobbylank
  • 1,906
  • 7
  • 15