How to dimension fill a string? Or how to have a dimension value that isn't in my dataset (Community)

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} ;;

}

...

}