To avoid repeated code, the substitution operator ${}
can be used to reference fields that have already been defined in LookML. This operator can appear in four forms with slightly different applications:
Referencing a Field in the Underlying Table
Using ${TABLE}.field_name
references a column name from the table in your database that is associated with the current view
. In the case of a derived table, it refers to columns in your outermost SELECT
clause. Here is an example (note that transformations can be performed on fields referenced this way).
In this case, the UPPER()
function in the SQL section, is being used to capitalize the last name:
view: users {
dimension: first_name {
sql: ${TABLE}.first_name;;
}
dimension: last_name {
sql: UPPER(${TABLE}.last_name) ;;
}
}
Referencing a Field Defined in the Same View
LookML can be self-referential: fields you have already defined in the same view can be re-used in other fields using ${field_name}
. The following example builds on the code above:
dimension: full_name {
sql: ${first_name} || ' ' || ${last_name} ;;
}
Because last_name has already been defined as all uppercase, when the field is referenced again in a new dimension, the same transformation is applied.
Referencing a Field Defined in a Different View
You may wish to perform calculations that draw from multiple views at times. As long as the view, from which you want to draw a field, is joined to the view you are working in, you can use ${view_name.field_name}
to reference that field.
The syntax of an example looks like:
view: users {
dimension: first_name {
sql: ${TABLE}.first_name ;;
}
dimension: last_name {
sql: UPPER(${TABLE}.last_name) ;;
}
dimension: full_name {
sql: ${first_name} || ' ' || ${last_name} ;;
}
dimension: full_name_two {
sql: ${first_name} || ' ' || ${other_view.middle_name} || ' ' || ${last_name} ;;
}
}
Again, when this reference style is used, in the dimension: full_name_two
, the other_view must be joined to the users view in the model file. If you do not join it, an unknown field error will appear.
Referencing a Persistent Derived Table Name
This feature can be useful when using persistent derived tables (PDTs).
When you have created a PDT in Looker, and then wish to reference that table in another PDTs SQL section, you'll need to use ${name_of_pdt.SQL_TABLE_NAME}
.
"SQL_TABLE_NAME" is a literal string after the name of the PDT being referenced; you do not need to replace it with anything. Here is a an example of the usage:
view: super_table {
derived_table: {
sql:
SELECT
something AS field_a
, something_else AS field_b
, other_thing AS field_c
FROM table ;;
}
}
view: super_duper_table {
derived_table: {
sql: |
SELECT
field_a
, field_b
, field_c
FROM ${super_table.SQL_TABLE_NAME};;
}
}