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