My use case is the following:
I have many views with a “country” dimension that is populated with ISO-8601 country codes, I would like to create another dimension at LookML level to avoid having to re-write the mapping and edit it in a single place in case I forgot or want to modify a mapping.
My first solution works and consists in writing a dimension with a sql CASE like so:
dimension: country_name {
type: string
map_layer_name: countries
sql:
CASE
WHEN ${TABLE}.country = "IT" THEN "Italy"
WHEN ${TABLE}.country = "DE" THEN "Germany"
WHEN ${TABLE}.country = "ES" THEN "Spain"
WHEN ${TABLE}.country = "FR" THEN "France"
WHEN ${TABLE}.country = "AT" THEN "Austria"
…
}
I would like to include this code in every view withouth repeating it, is there a way to include this code in my views, have a common view to derive this field from? or am I missing something else entirely?
The way we’ve solved it is to have a country dimensional table in our DWH.
country_code country_name local_currency region_name enabled
-------------- ---------------- ---------------- ------------- ---------
no Norway NOK SCA&CEE true
se Sweden SEK SCA&CEE true
es Spain EUR Atlantic true
fr France EUR true
pl Poland PLN SCA&CEE true
it Italy EUR true
de Germany EUR true
gb United Kingdom GBP Atlantic true
pt Portugal EUR Atlantic true
at Austria EUR SCA&CEE true
ie Ireland EUR Atlantic false
... ... ... ... ...
br Brazil BRL true
Then we’d have all these fields as dims in it’s own, which then is joined to an explore or extended with other views.
If adding this to your Source is not an option, you can also add these to a PDT that never refreshes on its own (the data is static pretty much).