Change join type dynamically with filters or parameters?

jdufault
Participant I

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! 

Solved Solved
0 5 2,045
1 ACCEPTED SOLUTION

Dawid
Participant V

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 solution in original post

5 REPLIES 5

Dawid
Participant V

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 🙂

jdufault
Participant I

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!

Dawid
Participant V

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?

jdufault
Participant I

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 … 

Dawid
Participant V

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

Top Labels in this Space
Top Solution Authors