Solved

Change join type dynamically with filters or parameters?


Hello everyone, 

I have a bit of a particular situation and was hoping for a workaround. 

Our Purchase table is inner joined to our Product table. This is particularly useful because it automatically shows only products that have had sales. However, I have a team that would like to have a holistic view at the products regardless of sales in the same view so they can analyze what is working well or not within a portfolio. 

Would there be a way to create a filter or a parameter that could be used as a filter to change the nature of the join from an inner to a left_outer? 

I realize we could simply change the join and then filter the results but it would mean going back through so many dashboard and looks and isn’t doable right now. 

Thank you for your help! 

icon

Best answer by Dawid 1 June 2021, 19:19

Thanks to a quick conversation with Looker support, it is possible but you would need to decide how.

If you want to leave the same view and explore, you may have to add a parameter that will feed to either sql_where or sql_always_where in the join.

  1. Change your join type to left_outer
  2. Create parameter in your view make_inner” with two values: “Yes” and “No”. Give it default “Yes

parameter: make_inner {
type: unquoted
allowed_value: {
label: "Yes"
value: "Yes"
}
allowed_value: {
label: "No"
value: "No"
}
default_value: "Yes"
}
  1. Add liquid statement in the explore
sql_always_where: {% if view_name.make_inner._parameter_value != "No" %} ${purchases.id} IS NOT NULL {% endif %} ;;

The code makes sure that even if parameter is not selected, which would be default behaviour for most of your analysis, this code ${purchases.id} IS NOT NULL makes de facto an INNER JOIN.

 

For your new purpose you can educate your stakeholders in question to use parameter, in case they want to have a left join. You can rename the parameter and refactor it to sound better :)

View original

5 replies

Userlevel 6
Badge +1

Thanks to a quick conversation with Looker support, it is possible but you would need to decide how.

If you want to leave the same view and explore, you may have to add a parameter that will feed to either sql_where or sql_always_where in the join.

  1. Change your join type to left_outer
  2. Create parameter in your view make_inner” with two values: “Yes” and “No”. Give it default “Yes

parameter: make_inner {
type: unquoted
allowed_value: {
label: "Yes"
value: "Yes"
}
allowed_value: {
label: "No"
value: "No"
}
default_value: "Yes"
}
  1. Add liquid statement in the explore
sql_always_where: {% if view_name.make_inner._parameter_value != "No" %} ${purchases.id} IS NOT NULL {% endif %} ;;

The code makes sure that even if parameter is not selected, which would be default behaviour for most of your analysis, this code ${purchases.id} IS NOT NULL makes de facto an INNER JOIN.

 

For your new purpose you can educate your stakeholders in question to use parameter, in case they want to have a left join. You can rename the parameter and refactor it to sound better :)

Thank you Dawid! That’s exactly what I was looking for. I did have to add an else statement with 1=1 to make it work. 

I appreciate your help!

Userlevel 6
Badge +1

Glad it worked. What SQL dialect do you use if you don’t mind me asking. I use BigQuery and didn’t need the 1 = 1 but I thought I would, is it SQL Server?

We use Oracle. If I didn’t put the else statement, I ended up getting empty brackets in my where statement whenever the  make_inner condition was set to No and Oracle did not like that!

where ( ) and … 

 

Userlevel 6
Badge +1

Ohh interesting! It’s good to know how other dialects behave ,hence thank you for the details!

Reply