How to search by Name from another view then filter by ID without joining both views

[Christel_Ilaka]

Knowledge Drop

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.

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

}

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

3. Add a sql_always_where parameter In the 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 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.