Search code examples
dashboardlooker-studio

Nested case statement in Data Studio

I have campaign names as "08-2019 ABCD - Compass" , "09-2019-DEFG Compass ebook" from which I would like to separate part of string but there is no pattern to separate the data.

I tried to write a nested case statement to create a new dimension by checking for "Compass" first and later "Compass ebook" but it throws up error as Invalid formula.

CASE 
WHEN REGEXP_MATCH(Campaign Name,"^.*(Compass).*") THEN 
   CASE 
    WHEN REGEXP_MATCH(Campaign Name,"^.*(Compass eBook).*") THEN "Compass eBook"
    ELSE "Compass"
   END
ELSE "Undefined" End

How do I achieve nested case statement scenario in Google data studio while creating a new dimension.

Solution

  • The CASE statement below does the trick (Linking both posts):

    CASE
  WHEN REGEXP_MATCH(Campaign Name, ".*(?i)(Compass eBook).*") THEN "Compass eBook"
  WHEN REGEXP_MATCH(Campaign Name, ".*(?i)(Compass).*") THEN "Compass"
  WHEN REGEXP_MATCH(Campaign Name, ".*(?i)(Testing Test).*") THEN "Testing Test"
  WHEN REGEXP_MATCH(Campaign Name, ".*(?i)(API Blockade Pattern).*") THEN "API Blockade Pattern"
  ELSE "Undefined"
END

    The Case statement uses a "First In First Out" type approach, thus setting the first WHEN clause to Compass eBook ensures that when REGEXP_MATCH sees the phrase Compass eBook it will be displayed first over the second REGEXP_MATCH that looks for Compass

    Google Data Studio Report to elaborate: