View the original community article here
Last tested: May 2, 2020
The Problem
Let say that we have two views: Rental
and Customer
. The Rental view has more than 1.5 billion records and each record has a customer_id
. The Customer view has more than 200k records with the following columns: customer_id
, first_name
, and last_name
.
Now we want to be able to filter the data in the Rental
view by entering the name of the customer in the filter suggestion; however, we know that the customer's name is stored in the Customer
view and we don't want to join these two views because of the number of records that we have in both views.
A Solution
This problem can be solve 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:
dimension: full_name_and_id {
type: string
sql: ${customer_id} || '-' || ${first_name} || '-' || ${last_name} ;;
}
- In the
Rental
view, create a filter-only field using 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 end-user to query the Customer
view via its explore without having to join both views (the explore customer
must exist in the project). The dimension created in step 1 or the full_name_and_id
dimension is queried to generated the suggested values. The values of this dimension are formatted as: ID-first_name-last_name
, for example 123-Christel-Ilaka
; by having this format, the end-users can type the name of the customer to see only the rentals of this customer.
Since the the Rental
view need 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 in line 5
is used to split the value entered in the filter; thus, creating an array called my_array
. Then the first element of this 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
check if the customer_id
is equal to the value stored in the variable the_id
and return yes
if that's the case, otherwise no
.
- Add a
sql_always_where
parameter In theexplore: rental
.
explore: rental {
sql_always_where:
{% if rental.namesearch._is_filtered %}
${rental.filtered_name} = 'yes'
{% else %}
1=1
{% endif %} ;;
}
The Liquid in line 3
verify if the parameter namesearch
is being used in the UI as a filter; if Yes 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 return all the data.
Screenshots of the generated SQL when the parameter namesearch
is not used (no filter is being applied)
Screenshots of the generated SQL with filter
|
This content is subject to limited support.