Multiple Case Statement To Clean Data

visualizations
lookml
(Nicholas Wong) #1

After plugging in a live connection, I realized that I could not build a map visualization… Why? Well, because the dataset isn’t clean… Not a great idea to create a dashboard based on raw dataset. :sweat_smile::sweat_smile:

But that’s ok because we can use CASE statement to clean it. Because I wanted to build a map visualization, users could key in US, U.S., United States, USA or U.S.A. or however they want. You get what I mean. However, I ran into an error when trying to do multiple CASE statement to one label.

Example:
when: { sql: ${TABLE}.“ELR_REG_COUNTRY” = ‘Afghanistan’ ;; label: “AF” }
when: { sql: ${TABLE}.“ELR_REG_COUNTRY” = ‘Afganisten’ ;; label: “AF” }
when: { sql: ${TABLE}.“ELR_REG_COUNTRY” = ‘afghanistan’ ;; label: “AF” }

Looker doesn’t recognize when you code it this way. They only take in the FIRST CASE Statement (Afghanistan = AF).

What you can do is add an OR statement. IN statement doesn’t work if you have a Snowflake database (yes, I tried it).

OR Case Statement:
when: {
sql:
${TABLE}.“ELR_REG_COUNTRY” = ‘Singapore’ OR
${TABLE}.“ELR_REG_COUNTRY” = ‘singapore’ ;; label: “SG” }

Hope this help if you’re facing an issue like me! :slight_smile:

2 Likes