Knowledge Drop

CASE in sql_on: Conditional Joins when field = value

  • 7 May 2021
  • 0 replies
  • 141 views

Last tested: Sep 21, 2020
 

It's possible to create a conditional join based on a field returning a specific value.**

If liquid syntax isn't working, we can use a CASE WHEN in the sql_on parameter:

sql_on: CASE

WHEN view_name.field_name = "specific value"

THEN ${view_name.key_id} = ${view_name_2.key_id}

ELSE ${view_name.id} = ${view_name_2.id}

END ;;

 

**Note that this use case would be extremely rare unless we're also filtering on field_name = "specific_value" at all times in the explore page, such as in a sql_always_where filter (in which case, we would not even need the CASE WHEN statement). If field_name is just selected on the explore page without any filters applied, Looker will not know which join statement to perform because the statement will evaluate to TRUE for one field_name value but FALSE for other field_name values.

If you want to use this type of join, you would need to filter down your explore results by field_name at all times, such as with an access_filter, but somehow limit that access_filter to never accept more than one value. Perhaps using a parameter with allowed_values would also work in these scenarios.

 

This content is subject to limited support.                

 

 

 


0 replies

Be the first to reply!

Reply