Question

Reusing cascading derived tables (with nested filters) in multiple views

  • 27 January 2017
  • 9 replies
  • 487 views

Hello,


I have a derived table (with a filter), let’s call it child_table, that I want to be able to reuse in various ways in other derived tables (parent_table_1, parent_table_2)


I found this thread, which shows that you can reference a filter in a parent table from the child table. For example, you can do this:


view: first_pass {
derived_table: {
sql: select ...
from raw_data_table
where {% condition second_pass.some_field %} some_column {% endcondition %}
group by 1
;;
}
}

view: second_pass {
derived_table: {
sql: select ...
from ${first_pass.SQL_TABLE_NAME}
group by 1
;;
}

filter: some_field {}
}

So, in my case, I want to find a way that I could reuse the SQL in first_pass (or “child table”) (which contains the filtering) in more than just second_pass (the “parent table”). However, in this case, the filters in first_pass strongly references second_pass (by using second_pass.some_field). This means that first_pass can only be used by second_pass.


Ideally, I would want something like this:


view: child_with_filter {
derived_table: {
sql: select ...
from raw_data_table
where {% condition second_pass.some_field %} some_column {% endcondition %}
group by 1
;;
}
filter: some_filter {}
}

view: parent_view_1 {
derived_table: {
sql: select ...
from ${child_with_filter.SQL_TABLE_NAME}
-- some other SQL here
;;
}

view: parent_view_2 {
derived_table: {
sql: select ...
from ${child_with_filter.SQL_TABLE_NAME}
-- some other SQL here, different than parent_view_2
;;
}

I realize that one way to do this is by joining child_with_filter in an explore; however, my SQL is complex enough that I’m not sure I can achieve the same using JOINs.


The obvious issue is that the filter defined on the child_table is not in scope when it makes its way into the parent tables: I always get an error like:

“sql_render template processing error: variable not found ‘some_filter’”


Is there any way to achieve this?


By the way, I’m using the filters to pass a parameter to my child table. The filter ends up looking something like:


   SELECT * FROM table where someDate < cast({% parameter some_filter %} as datetime)

9 replies

Huh, it looks like the solution is really just to do a dummy JOIN in the explore, as segahm mentions:



At first I’d assume I would have to actually use that JOIN in a meaningful way – but just getting that view in the explore is enough to reference the filters. You can even join on1=0 to prevent any actual join happening on the explore side (as in my case, where I want to do all joining/reference of the child_table in my own SQL)


facepalm

Userlevel 5
Badge

Glad you reached a solution @arie_litovsky!

A quick note on that error

sql_render template processing error: variable not found 'some_filter' -

Another common solution for that error is to prepend the view name to the filter name inside the liquid tags, even if it’s in the same view. Like

{% condition view_name.some_field %} or

{% parameter view_name.some_filter %}.

Just to be safe.

Hey Sam,

Interesting – I had actually tried that approach as well, but was still getting errors. I might give it another shot sometime.

Userlevel 5
Badge

Interesting! Feel free to visit us at help.looker.com when it comes to errors; we’re happy to dig in. Glad you were able to find a solution in this case!

I am dealing with a very similar scenario. I have 2 child views that run complex queries and I am joining both of them in a Parent view. I am joining the parent and both the child view in the model (a 1 to 1 more for a namesake to get the filter working) I have the actual join in the parent view.


I want the filter to be applied in the parent and the expectation is to get that propagated to the child. When I run the explore the filter is not getting propagated to the child views.


Do I need to mention the parent view name “parent_view” when I specify the filter as below in the child ? I am not able to do that since the looker throws validation error.



{% condition parent_view.filter_column %} some_column {% endcondition %}
Userlevel 4
Badge

@pradeep, what’s the error you’re referring to? You’ll have to scope it with the view name like you said, e.g. {% condition view_name.some_field %}. Would be great if you could pop on chat with our support team or send us the details to help.looker.com so we can have a deeper look.

I get the error variable not found view_name


For the below sample the error would be variable not found derived_fact


view: Base_fact {
derived_table: {
sql: select ...
fromdata_table
where {% condition **derived_fact.filter_field** %} some_column {% endcondition %}
group by 1;;
}
}
view: derived_fact {
derived_table: {
sql: select ...
from ${Base_fact.SQL_TABLE_NAME}
-- some other SQL here;;
}
filter: filter_field {}
}

Is there a standard way for reusing a view ? I have a few views that I will be referring in multiple other views and adding some logic on top. I am not able to do that if I simply join them in an Explore.

The alternate would be duplicate my complex base query in multiple places.

Userlevel 6
Badge

Yes you will need to use the full path for the field name view.field_name in order to get this to work.


What validation error are you getting?

I also ran into the variable not found issue when trying to get a filter to work on a parent view. I solved it by adding a bind_filter to the child view, like this:


   derived_table: {
explore_source: explore_name {
column: field_name { field: view_name.field_name }
bind_filters: {
to_field: view_name.filter_name
from_field: view_name.filter_name
}
}
}

Super weird, but it worked.

Reply