Knowledge Drop
Last tested: March 2021
The Problem
I need a condition to appear in the WHERE clause of my derived table, but I also need it to appear in the WHERE clause of my main Looker-generated query. So my final query should have the same condition in both places, like so :
WITH derived_table AS ( SELECT * FROM table WHERE <condition>)SELECT * FROM derived_tableWHERE <condition>
Solution 1: Using a filter field
In Docs, we have a great example of using a filter field to pass a condition into a derived table.
view: customer_facts { derived_table: { sql: SELECT customer_id, SUM(sale_price) AS lifetime_spend FROM order WHERE {% condition order_region %} order.region {% endcondition %} ;; } filter: order_region { type: string }}
However, this only passes the condition into the derived table. The condition will not appear in the main query.
To have the condition appear in the main query, we take advantage of the fact that the sql parameter of a filter field is placed directly into the WHERE clause of the main query. By using templated filters in this sql parameter, we apply the filter condition to any dimension we want.
view: customer_facts { derived_table: { sql: SELECT customer_id, SUM(sale_price) AS lifetime_spend FROM order WHERE {% condition order_region %} order.region {% endcondition %} ;; } filter: order_region { type: string sql: {% condition order_region %} ${region} {% endcondition %} ;; } dimension: region { type: string sql: ${TABLE}.region ;;}
Solution 2: Capturing a dimension's filter condition
Maybe you appreciate the solution above, but you don't like the extra step of creating an order_region filter. Maybe you'd prefer to just filter directly on the region dimension and have that filter condition also apply to the derived table.
This can be done by referencing the dimension name inside the condition tags, like so!
view: customer_facts { derived_table: { sql: SELECT customer_id, SUM(sale_price) AS lifetime_spend FROM order WHERE {% condition region %} order.region {% endcondition %} ;; } dimension: region { type: string sql: ${TABLE}.region ;;}
Solution 3: (6.20+) Use NDTs and bind_all_filters
Maybe you want this behavior to apply to ALL of your filters. In that case, the above solutions will suffice, but will take a lot of time to implement. If you're using NDTs, an easy solution is to use the bind_all_filters parameter! There is an example use case in this community article.
LinkedIn
Twitter
