View the original community article here
Last tested: Mar 20, 2020
- Easy, but more brittle way: LookML case
For example, this code:
dimension: status {
type: string
case:
when: { sql:${TABLE}.status = "complete" ;; label:"complete" }
when: { sql:${TABLE}.status = "pending" ;; label:"pending" }
when: { sql:${TABLE}.status = "cancelled" ;; label:"cancelled" }
when: { sql:${TABLE}.status = "other" ;; label:"other" }
}
Outputs this:
Even though only complete, pending, and cancelled are actually in the dataset.
- Less brittle, more nuanced way: Derived Table + Join Logic
Create a derived table view with the desired strings like so:
view: glossary {
derived_table: {
sql:
select 'complete' as x
union all
select 'pending' as x
union all
select 'cancelled' as x
union all
select 'other' as x ;;
}
dimension: glos {
sql: ${TABLE}.x
}
Then, use this glossary
view as the base view for your explores:
explore: my_explore {
view_name: glossary
join: viewa {
type: full_outer
sql_on: ${glossary.glos} = ${viewa.status} ;;
}
...
}
This content is subject to limited support.