Liquid variable reference

Liquid is a templating language that you can use in Looker to create more dynamic content. For example, you could build URLs to external tools based on the results of a query, or change which database table is queried based on a user's selection.

Liquid statements are built from variables, filters, and tags. Variables contain information that you want to use, and the variables that Looker provides are described on this page. You can further modify those values by using filters and tags, which you can read about in this Liquid guide .

There are several places in LookML that you can use Liquid:

• The action parameter
• The description parameter of a field (but not of an Explore)
• The html parameter
• Label parameters at the field level, including the label parameter, view_label parameter, group_label parameter, and group_item_label parameter
• The link parameter
• Parameters that begin with sql (such as sql and sql_on )
• The default_value dashboard filter parameter
• The filters dashboard element parameter

Using Liquid variables

Basic usage of Liquid variables is straightforward. Once you've identified the variable you'd like to use (see the following list), you can insert it into a valid LookML parameter. The specific Liquid variables that you can use in specific LookML parameters are defined next.

Two kinds of Liquid usage

There are two ways to make use of a Liquid variable:

1. Output Syntax: This type of usage can insert text and is probably the most common way to use Liquid in Looker. In this method, you enclose the Liquid variable in two curly braces. For example: {{ value }}
2. Tag Syntax: This type of usage usually doesn't insert text; instead, it is for logical comparisons and other Liquid operations. In this method, you enclose the Liquid variable in one curly brace and a single percent sign. For example: {% if value > 10000 %}

Basic examples

This section provides examples of using Liquid variables in the following ways:

• Using Liquid with the html parameter to generate product images
• Using Liquid with the url parameter to dynamically insert a selected value into a URL
• Using Liquid with the sql_table_name parameter to set the database table based on the field that the user chooses
• Using Liquid with the label parameter to change the label for a dimension depending on the LookML model name

Using Liquid with the html parameter to generate product images

In this example of HTML usage, a product ID is being inserted into an <img> tag to generate product images:

 dimension: product_image {
  sql: ${product_id} ;;
  html: <img src="https://www.altostrat.com/product_images/{{ value }}.jpg" /> ;;
} 

Using Liquid with the url parameter to dynamically insert a selected value into a URL

In this example of URL usage, an artist name is being inserted into a URL to produce a Google Search for that artist.

 dimension: artist_name {
  sql: ${TABLE}.artist_name ;;
  link: {
    label: "Google"
    url: "https://www.google.com/search?q={{ value }}"
    icon_url: "https://google.com/favicon.ico"
  }
} 

Using Liquid with the sql_table_name parameter to set the database table based on the field that the user chooses

In this example of SQL usage, the database table is being determined according to which fields the user chooses. The syntax uses an if, else if, else structure (denoted by if , elsif , and else ) to check and react to the fields included in the query.

 sql_table_name:
  {% if event.created_date._in_query %}
    event_by_day
  {% elsif event.created_week._in_query %}
    event_by_week
  {% else %}
    event
  {% endif %} ;; 

Using Liquid with the label parameter to change the label for a dimension depending on the LookML model name

In this example of label usage, the email dimension changes its label value depending on the LookML model name. This will dynamically change the name of the field in the field picker and in any query results that include the email dimension:

 dimension: email {
  label: "{% if _model._name == 'thelook' %} Looker Registered Email Address {% else %} External Email Address {% endif %}"
  type: string
  sql: ${TABLE}.email ;;
} 

For additional usage examples, see the individual LookML parameter page you're interested in.

Accessing variables from other fields

Liquid variables are usually based on the field where they are being used. However, you can also access values from other fields if needed.

Use the format {{ view_name.field_name._liquid-variable-name }} to access other fields from the same row in the query result. Replace _liquid-variable-name with any of the Looker Liquid variables. Make sure that the variable name is preceded by an underscore if it isn't normally, as follows:

• {{ view_name.field_name._value }}
• {{ view_name.field_name._rendered_value }}
• {{ view_name.field_name._model._name }}

This example shows this type of usage to access a website URL from a different field:

 dimension: linked_name {
  sql: ${name} ;;
  html: <a href="{{ website.url._value }}" target="_blank">{{ value }}</a> ;;
} 

When you reference another field with the {{ field_name._value }} Liquid variable syntax, the referenced field is added to the SELECT clause of the SQL query and added as an additional column in the GROUP BY clause. This is necessary to properly retrieve the values in the referenced field. However, it can cause unexpected results in the following areas:

• Aggregate measures — For more information, see the section on using Liquid variables in aggregate measures on this page.
• Chart downloads — When used in a query that is rendered as a table chart, if the table chart columns have been manually rearranged, some download formats of the chart display columns in their original orders .

Liquid variable definitions

The following table describes the Liquid variables that you can use with LookML. The Usagecolumn indicates which LookML parameters each Liquid variable can be used with, and includes the following options:

A = Works with the action parameter.

DV = Works with the default_value (for dashboards) parameter.

DE = Works with the description parameter at the field level, but won't work with description at the Explore level.

F = Works with the filters (for dashboard elements) parameter.

H = Works with the html parameter.

LA = Works with the label parameters at the field level, including the label parameter, view_label parameter, group_label parameter, and group_item_label parameter, but won't work with label parameters at the model, Explore, view, or reference line level, or with label as a subparameter of link .

LI = Works with the link parameter.

S = Works with all LookML parameters that begin with sql (e.g. sql , sql_on , and sql_table_name ).

Usage of date_start and date_end

The date_start and date_end Liquid variables are very useful for database dialects that partition data into multiple tables by date, such as BigQuery. You must use the tag syntax {% date_start date_filter_name %} or {% date_end date_filter_name %} . You cannot use the output syntax {{ date_start date_filter_name }} or {{ date_end date_filter_name }} , even though this syntax would typically be used to generate text.

For example, you can create these fields in a view:

 filter: new_filter_test{
  type: date
}

dimension: filter_start{
  type: date
  sql: {% date_start new_filter_test %} ;;
}

dimension: filter_end{
  type: date
  sql: {% date_end new_filter_test %} ;;
} 

If you filter an Explore on new_filter_test using the date range April 1, 2022 to May 25, 2022, the filter_start dimension would evaluate to April 1, 2022; the filter_end would evaluate to May 25, 2022.

Note the following about date_start and date_end :

• If the user doesn't filter the query using the filter that is specified in the date_filter part of the Liquid variable, both {% date_start date_filter %} and {% date_end date_filter %} will evaluate to NULL.

• If the user filters the query with an open-ended range on the filter that is specified in the date_filter part of the Liquid variable, the open end of the range will resolve to NULL. For example, using the example , if in the Explore a user sets the new_filter_test to before 2022-06-07, the {% date_start date_filter %} output will be NULL, since the user specified a range that has an end date but no start date. If a user sets the new_filter_test to after 2022-06-07, the {% date_end date_filter %} output will be NULL.

In either of these scenarios where the Liquid output may show a result of NULL, be sure to include a SQL function in the sql parameter to account for NULL values, such as IFNULL or COALESCE , depending on your database dialect.

See the Using date_start and date_end Best Practices page for an in-depth explanation on how to use the date_start and date_end Liquid variables to deal with date-partitioned tables.

See the Analytic Block Flexible period-over-period analysis Community post for an example of using date_start and date_end for flexible period-over-period analysis.

Usage of _in_query , _is_selected , and _is_filtered

Note that the _in_query , _is_selected , and _is_filtered variables provide either a true or a false value, as shown in this basic example of SQL usage . Consequently, choosing the proper type of Liquid variable reference is important.

If you want to determine whether or not something is included in your query, then insert certain text based on that, you should use a pattern like this:

 {% if view_name.field_name._in_query %}
  something to insert if true
{% else %}
  something to insert if false
{% endif %} 

If you want to literally insert the word "true" or "false", use a pattern like this:

 {{ view_name.field_name._in_query }} 

Some SQL dialects don't support the literal words "true" and "false". In that case, you can add the sql_boolean filter to get the true and false values you need:

 {{ view_name.field_name._in_query | sql_boolean }} 

The same patterns apply to the _is_selected and _is_filtered variables.

Usage of Liquid variables with the label parameter

You can use Liquid variables in a field's label parameter to dynamically change the field's appearance in the field picker and in visualizations. See the table in the Liquid variable definitions section on this page to see which Liquid variables will work with the label parameter.

Liquid variables work with label parameters at the field level, including the label parameter, view_label parameter, group_label parameter, and group_item_label parameter, but won't work with label parameters at the model, Explore, view, or reference line level, or with label as a subparameter of link .

The following variables can be used with label to affect the field picker, column headers in the data section of an Explore, and visualizations:

• _model._name
• _view._name
• _explore._name
• _field._name
• _user_attributes['name_of_attribute']

The other Liquid variables marked with LAin the Liquid variable definitions table , such as those that return a value based on a filter (like _filters ) or require that a query be run before the variable value can be determined (like in_query ), won't change the name of the field in the field picker. In those cases, the field name will only be changed in the resulting visualization.

When using the parameter Liquid variable with label , label is passed the value of the value subparameter.

Usage of Liquid variables with the description parameter

You can use Liquid variables with the description parameter to dynamically change the description for a field. This description appears when users hold the pointer over the field's information icon in the field picker , the field's column name in the data section of the Explore, or the field's column name in a table chart . See the table in the Liquid variable definitions section on this page to see which Liquid variables work with the description parameter.

Liquid variables work with the description parameter only at the field level. They won't work with the description parameter at the Explore level.

The following variables can be used with description to affect the field picker, the data section of an Explore, and the column header in a table chart :

• _model._name
• _view._name
• _explore._name
• _field._name
• _user_attributes['name_of_attribute']

The other Liquid variables marked with DEin the Liquid variable definitions table , such as Liquid variables that return a value based on a filter (like _filters ) or require that a query run before the variable value can be determined (like in_query ) won't change the description in the field picker or in the data section of an Explore. These Liquid variables will only affect the description shown when a user hovers over the field's column header in a table chart .

For examples of how to use Liquid in the description parameter, see the description parameter documentation page.

Things to consider

Check the Troubleshooting Liquid errors page for steps to resolve common errors. Some common scenarios are also described in the following sections.

Referencing yesno fields

To reference a yesno field's value, the value is case-sensitive. Use Yes or No . For example:

 {% if value == 'Yes' %} 

Using logical operators with Liquid variables

You can use the logical operators and and or with Liquid variables. Logical operators in Liquid are case-sensitive and must be written in all lowercase. For example:

 {% if value == "Shirt" or value == "Shoes" %}
  This is a shirt or shoes.
{% endif %} 

Getting the "Variable not found" error

If you are using a templated filter, then check whether you are referencing a table name that you have not joined into the derived table.

Naming conventions can affect query grouping

If there is a field with the name value, this field will be included in the GROUP BYclause of an Explore query whenever the value Liquid variable is referenced in another field within the same view.

For example:

 dimension: id {
  primary_key: true
  type: number
  sql: ${TABLE}.id ;;
  html:
    {% if value > 10 %}
      <font color:"darkgreen">{{ rendered_value }}</font>
    {% elsif value > 11 %}
      <font color:"goldenrod">{{ rendered_value }}</font>
    {% else %}
      <font color:"darkred">{{ rendered_value }}</font>
    {% endif %} ;;
}

dimension: value {
  sql: ${TABLE}.status ;;
  type: string
} 

This will generate the following SQL when only idis selected in an Explore:

 SELECT
orders.id AS orders.id,
orders.status AS orders.value
FROM order_items
LEFT JOIN orders ON order_items.order_id = orders.id

GROUP BY 1,2
ORDER BY orders.id
LIMIT 500 

To avoid this grouping behavior, make sure to scope the value variable with the name of the field to explicitly reference the field:

 dimension: id {
  primary_key: true
  type: number
  sql: ${TABLE}.id ;;
  html:
    {% if value > 10 %}
      <font color:"darkgreen">{{ id._rendered_value }}</font>
    {% elsif value > 11 %}
      <font color:"goldenrod">{{ id._rendered_value }}</font>
    {% else %}
      <font color:"darkred">{{ id._rendered_value }}</font>
    {% endif %} ;;
} 

Liquid variables in aggregate measures affect grouping

When you use the {{ view_name.field_name._value }} syntax or the {{ field_name._value }} syntax in the link or html parameter of a measure to reference a value from another field , Looker pulls that field into the SQL query to grab the field value. Because of this, Liquid can affect how SQL queries are generated and how many columns the GROUP BY clause uses, which can cause unexpected behavior when you're working with aggregate measures, such as measures of type: count .

For example, suppose you have the two following measures:

 measure: count_without_liquid {
  type: count
}

measure: count_with_liquid {
  type: count
  link: {
    label: "Status Count"
    url: "https://www.google.com/search?q={{ status._value }}"
  }
} 

When you generate a query using the count_without_liquid measure, you get the following results:

In this case, the query returns a single count for each month. The SQL that is generated for the previous results is shown next:

  SELECT 
  
 TO_CHAR 
 ( 
 DATE_TRUNC 
 ( 
 'month' 
 , 
  
 order_items 
 . 
 created_at 
  
 ), 
  
 'YYYY-MM' 
 ) 
  
 AS 
  
 "order_items.created_month" 
 , 
  
 COUNT 
 ( 
 * 
 ) 
  
 AS 
  
 "order_items.count_without_liquid" 
 FROM 
  
 order_items 
  
 AS 
  
 order_items 
 GROUP 
  
 BY 
  
 DATE_TRUNC 
 ( 
 'month' 
 , 
  
 order_items 
 . 
 created_at 
  
 ) 
 ORDER 
  
 BY 
  
 1 
  
 DESC 
 LIMIT 
  
 500 
 

However, when you generate a query using the count_with_liquid measure, you get the following results:

This example shows that, instead of a count for each month in the query, you receive a count for each month and for each status. That is because, in the generated SQL, the status field was added to the query so that its value could be retrieved. And, because it was added to the query, it was also added to the GROUP BY clause:

  SELECT 
  
 TO_CHAR 
 ( 
 DATE_TRUNC 
 ( 
 'month' 
 , 
  
 order_items 
 . 
 created_at 
  
 ), 
  
 'YYYY-MM' 
 ) 
  
 AS 
  
 "order_items.created_month" 
 , 
  
 order_items 
 . 
 status 
  
 AS 
  
 "order_items.status" 
 , 
  
 COUNT 
 ( 
 * 
 ) 
  
 AS 
  
 "order_items.count_without_liquid" 
 FROM 
  
 order_items 
  
 AS 
  
 order_items 
 GROUP 
  
 BY 
  
 DATE_TRUNC 
 ( 
 'month' 
 , 
  
 order_items 
 . 
 created_at 
  
 ), 
 2 
 ORDER 
  
 BY 
  
 1 
  
 DESC 
 LIMIT 
  
 500 
 

One option to stop this from happening is to use the row[] function with the Liquid variable, which pulls its value from the rendered results in the browser and therefore does not add the referenced field into the SQL query:

 link: {
    label: "{% if row['view_name.field_name'] %} some_label {% endif %}"
    url: "https://www.google.com/search?q={{ row['view_name.field_name'] }}"
  } 

When you're using this syntax, note that the link parameter works only if the field is selected or included in the query by some other means.

To sum up, the use of the row[] syntax won't cause the field to be added to the query like {{ field_name._value }} does. The dynamic label will cause the link to have no label if the field is not available, which causes the link to disappear from the link menu.