Filtering a View Using a Field from Another View without Joining Views

Looker Tips & Resources Technical Tips & Tricks
1

The Problem

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.

A Solution

This problem can be solved by using Liquid variables and the parameter parameter.

  1. 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.

  2. 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.

  3. 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.

  4. 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:

3 Likes