Error: Unknown field substitution


#1

Note: After version 3.16, these errors will no longer appear in Explore and are called Unknown or inaccessible field. Read about these errors and when they appear in the LookML Validator here.

Meaning of Error

When exploring data in Looker, you may see an Unknown or inaccesible field or Unknown field substitution error. This error usually means a dimension or measure is trying to be referenced that cannot be for some reason. You can also see this error in the LookML validator, described here.

Common Causes of this Error

  • One or more views contain a dimension or measure that references a field which does not exist
  • One or more views contain a dimension or measure that references a field to which the current explore does not have access (issue with joins)
  • A dimension created by a dimension_group is not being referenced correctly.

Solution

  1. Ensure the referenced dimension or measure is defined in your LookML and referenced correctly
  2. Join the view containing the referenced dimension or measure into the current explore

##Examples
####Example 1

You have an explore order_items and a view orders with the dimension order_id:

- explore: order_items
- view: orders
  fields:

  - dimension: order_id
    primary_key: true
    sql: ${TABLE}.order_id

In order to reference order_id from the order_items explore, orders must be joined into the order_item explore, like so:

- explore: order_items
  joins:
    - join: orders
      foreign_key: order_id

This would also be true if you wanted to reference the user_id from the users view:

- view: users
  fields:

  - dimension: user_id
    primary_key: true
    sql: ${TABLE}.user_id

users would need to be joined into the order_items explore in order to reference user_id:

- explore: order_items
  joins:
    - join: orders
      foreign_key: order_id
      
    - join: users
      foreign_key: orders.user_id

NOTE: If you are using sql_on or sql to join, you will need to use required_joins to get to users via orders.

####Example 2:

When referencing fields in a time dimension_group, the appropriate timeframe must be appended to the name of the field when the substitution operator is used. Consider the following view and dimensions:

- view: inventory_items
  fields:

  - dimension_group: created
    type: time
    timeframes: [date, month, year]
    sql: ${TABLE}.created_at

  - dimension_group: sold
    type: time
    timeframes: [date, month, year]
    sql: ${TABLE}.sold_at

You might wish to find the number of days between these two dates. The following would produce an unknown substitution error:

  - dimension: broken_datediff
    type: number
    sql: DATEDIFF(${sold},${created})

Instead, you would need to use the following:

  - dimension: functioning_datediff
    type: number
    sql: DATEDIFF(${sold_date},${created_date})

NOTE: If you do not append any timeframe, Looker will assume you are referencing the yesno timeframe on a time dimension_group, which returns ‘yes’ if there is a value, and ‘no’ for null values. If this is not a timeframe you have listed in timeframes it will return the Unknown Substitution Operator error.


Timeframes and Dimension Groups in Looker
Using Fields Parameters in LookML