Question

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

  • 27 January 2017
  • 9 replies
  • 1067 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