Question

Basic Case Statement

  • 16 October 2020
  • 7 replies
  • 665 views

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”

}

}


7 replies

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 ;;
}
Userlevel 6
Badge

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

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

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.

Userlevel 6
Badge

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 ;;
  }

Userlevel 6
Badge +1

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

Reply