Looker will not be updating this content, nor guarantees that everything is up-to-date.
Let's say that we have two views: Rental
and Customer
. The Rental
view has more than 1.5B records, and each record has a customer_id
. The Customer
view has more than 200K records with the following columns:
customer_id
first_name
last_name
We want to filter the data in the Rental
view by entering the name of the customer in the filter. However, the customers' names are stored in the Customer
view and we don't want to join these two views because of the number of records in both views.
This problem can be solved by using Liquid variables and the parameter
parameter.
In the Customer
view, concatenate the ID, first name, and last name to create a new dimension called full_name_and_id
:
dimension: full_name_and_id {
type: string
sql: ${customer_id} || '-' || ${first_name} || '-' || ${last_name};;
}
The values of this dimension are formatted as ID-first_name-last_name
, for example, 123-Christel-Ilaka
.
In the Rental
view, create a filter-only field called namesearch
using a Liquid parameter
:
parameter: namesearch {
type: string
suggest_explore: customer
suggest_dimension: customer.full_name_and_id
suggest_persist_for: "24 hours"
}
The suggest_explore
and suggest_dimension
parameters allow the user to query the Customer
view via its explore
without having to join both views (the explore
named customer
must exist in the project). The dimension created in step 1, full_name_and_id
, is queried to generate the suggested values.
Since the Rental
view needs to be filtered based on the ID
dimension, create a yesno
dimension that will be used to extract the ID
from the value entered by the user:
dimension: filtered_name {
hidden: yes
type: yesno
sql:
{% assign my_array = namesearch._parameter_value | remove: "'" | split: "-" %}
{% assign the_id = my_array[0] %}
${customer_id} = {{the_id}}
;;
}
The Liquid used with the sql
parameter in line 5 splits the value entered in the filter, creating an array called my_array
. Then the first element of this array is assigned to a variable, the_id
, in line 6. Finally, for each record in the Rental
view, the code in line 7 checks if the customer_id
is equal to the value stored in the variable the_id
and, if it is, returns yes
; otherwise, it returns no
.
Add a sql_always_where
parameter in explore: rental
.
explore: rental {
sql_always_where:
{% if rental.namesearch._is_filtered %}
${rental.filtered_name} = 'yes'
{% else %}
1=1
{% endif %} ;;
}
The Liquid in line 3 verifies whether the parameter namesearch
is being used in the Looker UI as a filter; if it is, then the query will only return records where filtered_name
is equal to yes
. If the parameter is not being used as a filter, the query returns all the data.
When the parameter namesearch
is not used to filter results (no filter is being applied), the generated SQL looks like:
When the parameter namesearch
is applied to filter results by a specific user, the generated SQL looks like: