home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
Looker documentation will be moving to cloud.google.com in mid-2022!
All the information you rely on will be migrated and all docs.looker.com URLs will be redirected to the appropriate page.
case

Usage

view: view_name {
  dimension: field_name {
    case: {
      when: {
        sql: SQL condition ;;
        label: "value"
      }
      # Possibly more when statements
      else: "value"
    }
    alpha_sort: yes
  }
}
Hierarchy
case
Possible Field Types
Dimension

Accepts
A SQL condition and a string

Special Rules
Use an alpha_sort parameter if you want the values alphabeticalized

Definition

case lets you 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. A SQL CASE statement will not create such a menu.

The general form of case is:

dimension: status { case: { when: { sql: condition ;; label: "Label of Condition" } # possibly more when statements else: "Label If No Condition Met" } }

These parameters work as follows:

The suggestions presented to a business user for a case dimension will not be restricted when using full_suggestions. Any option that you include in a case parameter will be visible to all users.

Choosing the sort order of labels with alpha_sort

Typically, case values appear in the order you write them. If you prefer an alphabetical sort, you can use alpha_sort: yes like this:

dimension: status { alpha_sort: yes case: { … } }

Examples

Assign several human-readable labels to different status numbers:

dimension: status { case: { when: { sql: ${TABLE}.status = 0 ;; label: "pending" } when: { sql: ${TABLE}.status = 1 ;; label: "complete" } when: { sql: ${TABLE}.status = 2 ;; label: "returned" } else: "unknown" } }

When the same condition is repeated and evaluates to different labels, LookML uses the first condition that evaluates to true. In the following example, ${TABLE}.status = 0 evaluates to pending and not returned, since the pending condition is evaluated first.

dimension: status { case: { when: { sql: ${TABLE}.status = 0 ;; label: "pending" } when: { sql: ${TABLE}.status = 1 ;; label: "complete" } when: { sql: ${TABLE}.status = 0 ;; label: "returned" } else: "unknown" } }

When multiple conditions evaluate to the same label, LookML uses only the first condition. In the following example, Looker will use ${TABLE}.status = 0 instead of ${TABLE}.status = 2 to generate the SQL CASE statement that evaluates to pending. When ${TABLE}.status = 2, the CASE statement evaluates to unknown.

view: orders dimension: status { case: { when: { sql: ${TABLE}.status = 0 ;; label: "pending" } when: { sql: ${TABLE}.status = 1 ;; label: "complete" } when: { sql: ${TABLE}.status = 2 ;; label: "pending" } else: "unknown" } }

Top