Have filters on joined view not change measures on explore view?

  • 11 April 2015
  • 2 replies

Userlevel 2

I’m using symmetric aggregates. I want to put filters on a field in a joined-in view, “B”, such that it affects measures of that view, but doesn’t affect measures of the explore view, “A”. Without thinking about the SQL needed to generate this query, I initially intuited that because of the ‘left outer’ nature of joins in Looker, this would be the behavior of filters on joined-in views in this situation, but it is not, either in Looker or SQL.

A row of the explore, “A”, shows up in the results if it does not match with any rows in “B”, or if it matches with rows in “B” which pass “B”'s filters. That’s a left outer join. But if a row in “A” matches only with rows in “B” which don’t pass “B”'s filters, that “A” row is not in the results.

In fact I don’t think this is really feasible in Looker. In SQL you’d put the condition from the WHERE clause into ON clause of the join. Or you could replace the joined-in table with a query on that table with a WHERE that would do the filtering you want. Then your left outer join would do what I want here.

Is there a way to solve my problem in Looker?

2 replies

Userlevel 4

Hi Ethan, if I understand correctly, you want to inject conditions into the join rather than the entire query’s where clause. If that is the case, there isn’t quite a way to do that flexibly, but we have some features coming down the pike that should do exactly what you are looking for. Lloyd just wrote a post on this actually: Conditions in Join Clauses (3.20+)

Edit: If you want to do this right now, for limited cases, you could create a similar situation using derived tables, using templated filters in the derived table:

Userlevel 2

That new feature, ‘Conditions in Join Clauses’, is exactly what I need. And yes, I could get the results I need with a derived table with templated filter, but I’m trying to avoid that when possible.