In SQL, there is more than one way to define conditional logic. Two primary ways that come to mind are IF
statements and CASE WHEN
statements.
In Looker, CASE WHEN
statements are the best practice for defining conditional logic.
We have a couple of examples below that show how to best use conditional logic in Looker.
Recommended Practice
CASE WHEN
statements can be used anywhere SQL can be used, such as in the sql
parameter of a dimension:
dimension: full_time_result { sql: CASE WHEN ${team_side} = 'away' AND ${TABLE}.ft_result = 'H' THEN 'Loss' WHEN ${team_side} = 'away' AND ${TABLE}.ft_result = 'A' THEN 'Win' WHEN ${team_side} = 'home' AND ${TABLE}.ft_result = 'H' THEN 'Win' WHEN ${team_side} = 'home' AND ${TABLE}.ft_result = 'A' THEN 'Loss' WHEN ${TABLE}.ft_result = 'D' THEN 'Draw' ELSE NULL END ;; }
Not Best Practice
Using SQL IF
statements in the sql
parameter is not considered a best practice in Looker:
dimension: full_time_result { sql: IF (${team_side} = 'home' AND ${TABLE}.ft_result = 'H', 'Win', IF (${team_side} = 'home' AND ${TABLE}.ft_result = 'A', 'Loss', IF (${team_side} = 'away' AND ${TABLE}.ft_result = 'H', 'Loss', IF (${team_side} = 'away' AND ${TABLE}.ft_result = 'A', 'Win', IF(${TABLE}.ft_result = 'D', 'Draw'))))) ;; }
Looker prefers the CASE WHEN
over the IF
statement because:
- The use of an
IF
statement can get messy. IF
statements are not supported by all dialects of SQL, and there are variations among dialects that do support them (Redshift, Postgres, MySQL).
See Using LookML CASE vs SQL CASE WHEN for a comparison of using SQL CASE WHEN
and the LookML case
parameter in Looker.