Question

Cascading Derived Tables with Nested Filters

  • 7 November 2016
  • 5 replies
  • 329 views

I’m not sure if I’m being dense. I have two derived tables (CTEs), the first of which uses a liquid filter. I’m failing to see how I can have a filter present in the definition of the second and have it apply to the first. Perhaps it’s just not possible.



Old LookML
```


  • view: first_pass

    derived_table:

    sql: |

    select …

    from raw_data_table

    where {% condition some_field %} some_column {% endcondition %}

    group by 1




  • view: second_pass

    derived_table:

    sql: |

    select …

    from ${first_pass.SQL_TABLE_NAME}

    group by 1

    /*

    if I create filter: some_field, it’s not in scope such that it’s

    applied to the SQL defined in first_pass.

    */




</details>
<details>
<summary>New LookML</summary>

view: first_pass {

derived_table: {

sql: select …

from raw_data_table

where {% condition some_field %} some_column {% endcondition %}

group by 1

;;

}

}

view: second_pass {

derived_table: {

sql: select …

from ${first_pass.SQL_TABLE_NAME}

group by 1

;;

}

}


/*

if I create filter: some_field, it’s not in scope such that it’s

applied to the SQL defined in first_pass.

*/


</details>

5 replies

Userlevel 3

I would love that feature. The way I solve this issue today is by joining the same table across all explores and defining the filters in that one view. In some cases, the join makes sense - in others, it is a dummy (1=1).

Userlevel 2

Hi Scott,


I believe you can use fully-scoped references inside the {% condition %} tag, so the field you’re filtering on can come from a different view.



Old LookML
```
- 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

    fields:

    • filter: some_field




</details>
<details>
<summary>New LookML</summary>

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 {}

}


</details>

Then you only need to join `second_pass` to your explore.

Let me know how that works!

Oh, and first_pass is exposed as its own base view. I suppose I could do this:



Old LookML
```
- view: first_pass
derived_table:
sql: |
select ...
from raw_data_table
where {% condition second_pass.some_field %} some_column {% endcondition %}
group by 1
fields:
- filter: second_pass.some_field

  • view: second_pass

    derived_table:

    sql: |

    select …

    from ${first_pass.SQL_TABLE_NAME}

    group by 1

    fields:

    • filter: some_field




</details>
<details>
<summary>New LookML</summary>

view: first_pass {

derived_table: {

sql: select …

from raw_data_table

where {% condition second_pass.some_field %} some_column {% endcondition %}

group by 1

;;

}


filter: second_pass.some_field {}

}


view: second_pass {

derived_table: {

sql: select …

from ${first_pass.SQL_TABLE_NAME}

group by 1

;;

}


filter: some_field {}

}


</details>

For each old LookML code block in this article and its comments, we just added the New LookML equivalent code.

We encountered this again today. We ended up using @segahm’s approach; however, I would love to see a solution or design pattern that doesn’t rely on a dedicated view of filter-only fields joined in wherever a filter is needed.

Reply