filter

Usage

view: view_name {
filter:filter_name{...}
}

Definition

Thefilterparameter declares a filter-only field and a name for that filter. A user can add filter-only fields as filters when exploring, but they cannot add them to their result set. These filter-only fields are made useful usingtemplated filters,which are an advanced LookML topic. You can also refer to theUsingfilterto filter by a hidden fieldexample.

The filter name must:

• Be unique within any given view
• Consist of charactersathroughz(no capital letters),0through9,or_
• Start with a letter

There are many types of filter fields, as discussed further on theDimension, filter, and parameter typesdocumentation page.

Examples

Here are some examples for using thefilterparameter.

Creating a user-specified filter

Create a filter that lets the user specify theorder_region:

filter: order_region {
type: string
}

Defining a dynamic derived table with a templated filter

As shown on theTemplated filters and Liquid parametersdocumentation page, define aderived tableto calculate the lifetime spending for customers in a region that is specified by the user. This example uses thefiltercreated in the previous example as part of a templated filter. Thefilterinput is used in theWHEREclause withLiquid variables:

view: customer_facts {
derived_table: {
sql:
SELECT
customer_id,
SUM(sale_price) AS lifetime_spend
FROM
order
WHERE
{% condition order_region %} order.region {% endcondition %}
GROUP BY 1
;;
}

filter: order_region {
type: string
}
}

Using thesqlparameter withfilter

You can also use thesqlparameter withfilter,which applies to the SQLWHEREclause whenever the filter has a value. This allows for a dynamicWHEREclause, based on the user filter input.

The following example creates a filter that allows only user names that exist in the dataset:

filter: user_enabled {
type: string
suggest_dimension: user_name
sql: EXISTS (SELECT user_id FROM users WHERE {% condition %} user_name {% endcondition %} and state = 'enabled');;
}

In the previous example, if the complete list of user names in the dataset is "Zach", "Erin", and "Brett", the filter results in the followingWHEREclause:

WHERE EXISTS (SELECT user_id FROM users WHERE user_name in ('Zach', 'Erin', 'Brett') and state = 'enabled')

See theUsingfilterto filter by a hidden fieldsection on this page for an example of how to use thesqlparameter withfilter.

Usingfilterto define a dynamic derived table and a user-defined filter

Using the earlier example that defines aderived tablewith a dynamic region value, you can use thesqlparameter with atemplated filterto dynamically build aWHEREclause that applies to both the derived table and the main Looker-generated query:

view: customer_facts {
derived_table: {
sql:
SELECT
customer_id,
SUM(sale_price) AS lifetime_spend
FROM
order
WHERE
{% condition order_region %} order.region {% endcondition %}
GROUP BY 1
;;
}
filter: order_region {
type: string
sql: {% condition order_region %} ${region} {% endcondition %};;
}
dimension: region {
type: string
sql: ${TABLE}.region;;
}

In the previous example, the user provides input to the filterorder_region,which in turn provides the value to theregiondimension. Theregiondimension then provides the value of theWHEREclause in the derived table SQL and, because of thesqlparameter in thefilterdefinition, the value for theWHEREclause in a Looker-generated query.

Usingfilterto filter by a hidden field

You can usefilterto create a dimension that users can filter on, while also preventing users from selecting the dimension in a query.

1. First, hide the dimension in question usinghidden: yes.This means that the dimension won't be available for users to select from an Explore field picker.

   dimension: field_to_hide {
   type: string
   hidden: yes
   sql: ${TABLE}.field_to_hide;;
   }
   

2. Now, make afilterfield to link to thefield_to_hidedimension.

   filter: filter_on_field_to_hide {
   type: string
   sql: {% condition filter_on_field_to_hide %} ${field_to_hide} {% endcondition %};;
   }
   

As discussed in theUsing thesqlparameter withfilterexample, thesqlparameter of thefilterfield applies SQL directly to theWHEREclause of the query. In this case, thesqltakes the filter condition specified in thefilter_on_field_to_hidefilter and applies it to the${field_to_hide}dimension.

This way, users can filter a query byfield_to_hidewith thefilter_on_field_to_hidefilter, while thefield_to_hidedimension remains hidden.