Usage
dimension: field_name {
case: {
when: {
sql: SQL condition ;;
label: "value"
}
# Possibly more when statements
else: "value"
}
alpha_sort: yes
}
}
Hierarchycase |
Possible Field TypesDimensionAcceptsA SQL condition and a stringSpecial RulesUse analpha_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:
These parameters work as follows:
when— You may use as manywhenstatements as you would like to represent each condition for which you want to supply a label. Thewhenstatements are evaluated in order from the first one listed to the last one listed, and the firstwhenstatement that is evaluated to true will assign the associated label.sql— Thesqlparameter 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 ofstring. The value of eachlabelin acasestatement must be unique. If you use the samelabelvalue for multiple SQL conditions, only the last SQL condition in thecasestatement is assigned thelabelvalue. See Examples on this page.else— If none of your conditions are met, this is the label that will be used.
The suggestions presented to a business user for a
casedimension will not be restricted when usingfull_suggestions. Any option that you include in acaseparameter 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:
Examples
Assign several human-readable labels to different status numbers:
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.
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.