Basic Case Statement

jwhitfield
Participant I

I’m having difficulty getting a seemingly simple case statement to work. Getting the following error when running:
“-- raw sql results do not include filled-in values”
Can anybody tell me why? Here is the code:

dimension: product_description {
case: {
when: {
sql: ${product_sku_dimension}=“abc123”;;
label: “abc”
}
when: {
sql: ${product_sku_dimension}=“xyz789”;;
label: “xyz”
}
else: “other”
}
}

0 7 3,472
7 REPLIES 7

eliott
Participant II

Have you tried using a regular CASE statement in your SQL parameter?

Something like:

dimension: product_description {
   type: string
   sql: 
      CASE
         WHEN ${product_sku_dimension} = “abc123” THEN "abc"
         WHEN ${product_sku_dimension} = “xyz789” THEN "xyz"
         ELSE "other"
      END ;;
}

I believe this isn’t an error, just an informational message. Looker has a feature called “dimension fill” where it will sometimes construct result sets where missing rows have been filled in (e.g. sequential dates or labels from a known set of possibilities like in this case)

So this message is just letting you know that some of the values you see from among “abc”/“xyz”/“other” may have been filled in by Looker, and not by the query result

jwhitfield
Participant I

tried the above code, get the error: invalid identifier “abc123”

jwhitfield
Participant I

This is a good point to call out here, though I’m definitely getting an error (query fails to run) in just the above basic case when syntax.

Other than the curly quotes (which I assume are Discourse’s doing), the LookML looks fine to me. Is there not another error message shown? Sometimes it helps to “open in SQL runner” for more detail if it’s a SQL related error.

Also, chat support would probably be able to help you get to the bottom of this pretty quick either way!

I am having a hard time with creating a custom dimension. Below is the code I created from the forum, but I get the error “incomplete statement”. Would someone be able to help? Thanks!

dimension: Sector {
  type: string
  sql: 
CASE
WHEN ${d_user.pl_segment} = "Technology" AND ${d_user.pl_strategy} = "Enterprise" then "TECH"
  WHEN ${d_user.pl_segment} = "Medical Devices & Diagnostics" AND ${d_user.pl_strategy} in ("Enterprise","Growth") then "MDD",
  WHEN ${d_user.pl_segment} = "Life Sciences" AND ${d_user.pl_strategy} in ("Enterprise","Growth") then "PHARMA"
  WHEN ${d_user.pl_segment} = "Industrials" AND ${d_user.pl_strategy} in ("Enterprise","Growth") then "IND"
  WHEN ${d_user.pl_segment} = "Consumer / Financial Services" AND ${d_user.pl_strategy} in ("Enterprise","Growth") then "Growth"
  WHEN ${d_user.pl_segment} = "Technology" AND ${d_user.pl_strategy} = "Growth" then "GROWTH"
  ELSE NULL END ;;
  }

Dawid
Participant V

You definitely don’t need the comma after “MDD”

Top Labels in this Space
Top Solution Authors