How to write LookML CASE in Looker

Case enables you to bucket results with case logic. While you can write raw SQL CASE statements instead, using case will create a drop-down menu for your users in the Looker UI.

These are primary use cases where using LookML case is beneficial to use

• You can convert numeric codes to human-readable labels (same as SQL CASE).
• You can Create a pick list for filters (which is NOT possible with SQL CASE).

Examples

dimension: Segment {
  sql: CASE
  WHEN ${TABLE}.status = 0 THEN 'Small'
  WHEN ${TABLE}.status = 1 THEN 'Medium'
  WHEN ${TABLE}.status = 2 THEN 'Enterprise'
  ELSE 'Summer'
  END ;;
}

These parameters work as follows:

• when — You may use as many when statements as you would like to represent each condition for which you want to supply a label. The when statements are evaluated in order from the first one listed to the last one listed, and the first when statement that is evaluated to true will assign the associated label.
• sql — The sql parameter accepts a SQL condition that evaluates to true or false.
• label — If the SQL condition is true, this is the label that will be assigned. The assigned label has a data type of string. The value of each label in a case statement must be unique. If you use the same label value for multiple SQL conditions, only the last SQL condition in the case statement is assigned the label value.
• else — If none of your conditions are met, this is the label that will be used.

More Details

_______________________________________________________________________________________

In Looker, a case statement is used to create a new dimension or measure that is based on the values of another dimension or measure. It allows you to create a new field that has different values depending on the values of another field. The basic syntax for a case statement in LookML is as follows:

case
  when [dimension or measure] = [value] then [output value]
  when [dimension or measure] = [value] then [output value]
  ...
  else [output value]
end

For example

you might use a case statement to create a new dimension called “status” that is based on the values of a dimension called “status_code”. You might define the new dimension as follows:

dimension: status {
  type: string
  case:
    when status_code = 1 then "active"
    when status_code = 2 then "inactive"
    when status_code = 3 then "deleted"
    else "unknown"
  end
}

This will create a new dimension called “status” that has the values “active”, “inactive”, “deleted” or “unknown” depending on the value of the “status_code” dimension.

What is Looker BI?

How Looker Writes SQL?

How to calculate percent of total in looker?

Table Calculations in Looker

What are Derived tables in Looker?

How to set conditional formatting in LOOKER?

Looker Interview Questions And Answers

In a looker case statement, you can specify one or more when clauses, each of which tests whether a certain condition is true. The first when clause that is true will have its corresponding then value returned. If none of the conditions are true, the else value will be returned.

For example, if you wanted to create a new dimension called “status” based on the values of a dimension called “status_code”. You could use a case statement like this:

dimension: status {
  type: string
  case:
    when status_code = 1 then "active"
    when status_code = 2 then "inactive"
    when status_code = 3 then "deleted"
    else "unknown"
  end
}

In this example, the dimension “status” will have the values “active”, “inactive”, “deleted” or “unknown” depending on the value of the “status_code” dimension.

Another example would be for measure where you want to apply different calculations based on the value of another dimension

measure: total_price {
  type: sum
  sql: ${price}
  case: 
    when category = 'book' then ${price} * 1.2
    else ${price}
  end
}

This example a measure called “total_price” that is calculated as the sum of the “price” field, but for books it will be multiplied by 1.2.

You can also use case statements in conjunction with other functions and aggregations in LookML. This allows you to create more complex and powerful calculations and dimensions.

Use Cases

A common use case for using case statements in LookML is to create a new dimension or measure based on multiple conditions. For example, you may have a dimension called “age” and you want to create a new dimension called “age_group” that categorizes the age into different groups like “kids”,”teen”,”adult”, “senior citizen”. You could use a case statement like this:

dimension: age_group {
  type: string
  case:
    when age < 18 then 'kids'
    when age >= 18 and age <= 25 then 'teen'
    when age > 25 and age <= 60 then 'adult'
    when age > 60 then 'senior citizen'
    else 'unknown'
  end
}

This will create a new dimension called “age_group” that has the values “kids”, “teen”, “adult”, “senior citizen” or “unknown” depending on the value of the “age” dimension.

You can also use case statement in combination of other functions and operators, like coalesce, ifnull, is_null or nullif to handle null values or missing data. You can also use Looker’s lookup function in conjunction with a case statement to perform more complex data transformation.

Here is an example that uses a lookup function

dimension: region {
  type: string
  case:
    when postal_code in lookup('zip_to_region', 'zip_code', 'region') then zip_code
    else 'unknown'
  end
}

This will create a new dimension called “region” and will lookup in the ‘zip_to_region’ view, to map the postal_code to the corresponding region.

It’s also worth mentioning that you can use case statement in the SQL of the measure, allowing you to use the case statement directly on the database level. This can help improve the performance of your queries when working with large data sets.

Please let me know if you have any more question about using the case statement in Looker.

Take a look at the Looker Product, Also Reach out to us here if you are interested to evaluate if Looker is right for you.

About Me:-

I am Om Prakash Singh – Data Analytics Consultant , Looker Consultant , Solution Architect .
I am Highly analytical and process-oriented Data Analyst with in-depth knowledge of database types; research methodologies; and big data capture, manipulation and visualization. Furnish insights, analytics and business intelligence used to advance opportunity identification.

You’ve got data and lots of it. If you’re like most enterprises, you’re struggling to transform massive information into actionable insights for better decision-making and increased business results.
Reach out to us here if you are interested to evaluate if Looker is right for you or any other BI solution.