Solved

Dynamic Joins based on filters

  • 15 January 2018
  • 8 replies
  • 222 views

Is it possible to use Liquid to dynamically define a join in an Explore based on certain filters?


For example


explore: name {
...
}
{% if filter = 'x' %}
join: join_x {
...
}
{% elseif filter = 'y' %}
join: join_y {
...
}

By selecting a certain filter in the Explore the different join would appear. This would also need to update the UI to show the available dimensions/measures from the view based on the join.


Is this something that is possible?

icon

Best answer by will.adams 17 January 2018, 01:45

Hi Luke, you’re correct, that was my bad, _filters['...'] is not available in parameters that begin with sql_ I found in the doc I posted above. So what you would want would be to set up a parameter to manually toggle the join back and forth.



It sounds like what you’re trying to do would be more easily accomplished by creating a few separate, more modular explores. The explore itself will not dynamically rearrange itself based on the contents of a look the way you describe, so you would have to have each join in the explore consider each of the configurations you’d want to have. This will get messy pretty quickly, so I would recommend multiple explores.

View original

8 replies

Userlevel 6
Badge

I have not played with this for a while but when I was attempting something similar the SQL generation engine seems to grab everything it might need and then did its substitutions...meaning when I had a parameter with 3 field names in (that I was using to build a dynamic dimension) which came from 3 different views the query resulted in all 3 of those views being joined in even though 2 of them were not required. I was told that this is because of the order the SQL generation & the liquid evaluation run in.

@receipt-banklm did you ever find a way to do this?

Hi @romain.ducarrouge ,

Has there been an update on this feature?

Thank you,

Diego

Userlevel 3
Badge

Hey @DaanIF the approach mentioned by Will is the best way to achieve this at this time. I will send a note to our product team to let them know you would like to have this feature in Looker. Thanks for the feedback.

I am interested in seeing this being able too. I would like to join a view under specific filtering conditions, exactly as Will posted in his first reply.


Is there no solid workaround for this? It looks so intuitive…

Hi Luke, you’re correct, that was my bad, _filters['...'] is not available in parameters that begin with sql_ I found in the doc I posted above. So what you would want would be to set up a parameter to manually toggle the join back and forth.


It sounds like what you’re trying to do would be more easily accomplished by creating a few separate, more modular explores. The explore itself will not dynamically rearrange itself based on the contents of a look the way you describe, so you would have to have each join in the explore consider each of the configurations you’d want to have. This will get messy pretty quickly, so I would recommend multiple explores.

Hey Will. Thanks for the help. I tried to implement this and got the error Cannot user "_filers" in SQL.


It seems like the above example would allow for dynamically defining the JOIN to another_view. I am looking for something that will essentially define a whole new join block based from a filter from the base_view. This would then appear as a new block of dimensions and fields in the UI when the event is selected but otherwise these would be hidden. I am not sure if this is possible?


Thanks for the response.


Comment edited for clarity: Best way to do this is outlined here: https://docs.looker.com/reference/explore-params/sql_on#conditional_joins


Reply