Question

Using Case statements in Filter in Looker Views

  • 18 August 2021
  • 1 reply
  • 119 views

I am adding CASE statements to my filter in my views. I basically want that if the user types `Harry` the filtering should happen on `Gryffindor`. This is my code:

filter: house {
    # type: string
    # sql: ${TABLE}.house ;;
    sql:  CASE
            WHEN ${TABLE}.house LIKE '%harry%' THEN ${TABLE}.house = 'gryffindor'
            WHEN ${TABLE}.house LIKE '%luna%' THEN ${TABLE}.house = 'ravenclaw'
            WHEN ${TABLE}.house LIKE '%ernie%' THEN ${TABLE}.house = 'hufflepuff'
            WHEN ${TABLE}.house LIKE '%draco%' THEN ${TABLE}.house = 'slytherin'
          ELSE ${TABLE}.house = ${TABLE}.house END ;;
  }

When I go to explore and type out `harry` in my filter, it is returning all the values like:

gryffindor
ravenclaw
hufflepuff
slytherin
other

This is what my SQL code is looking like:

SELECT
    "house" AS "hogwarts.house"
FROM
    "schools"."hogwarts" AS "hogwarts"
WHERE (CASE
            WHEN hogwarts.house LIKE '%harry%' THEN hogwarts.house = 'gryffindor'
            WHEN hogwarts.house LIKE '%luna%' THEN hogwarts.house = 'ravenclaw'
            WHEN hogwarts.house LIKE '%ernie%' THEN hogwarts.house = 'hufflepuff'
            WHEN hogwarts.house LIKE '%draco%' THEN hogwarts.house = 'slytherin'
            ELSE hogwarts.house = hogwarts.house END )
GROUP BY
    1
ORDER BY
    1
LIMIT 500

This query will obviously return all the values. 

Is there a way that in the filter if I enter `harry` it only return:
gryffindor

 

 


This topic has been closed for comments

1 reply

Hello!

In this situation I believe a parameter would be easier to work with than a templated filter. I say that because we can call the parameter value in a liquid “if” statement in such a way that would allow for this ‘like’ comparison to happen. The parameter would be mostly bare:

  parameter: house_picker {
label: "Choose Your House"
type: string
}


 The comparison would then be moved from the parameter definition to the dimension in this case, creating a “dynamic” house dimension.

  dimension: dynamic_house {
label: "House"
sql: {% if house_picker._parameter_value contains 'harry' %} 'gryffindor'
{% elsif house_picker._parameter_value contains 'luna' %} 'ravenclaw'
{% elsif house_picker._parameter_value contains 'ernie' %} 'hufflepuff'
{% elsif house_picker._parameter_value contains 'draco' %} 'slytherin'
{% else %} ${TABLE}.house
{% endif %}
;;
}

This is leveraging the liquid “contain” functionality, there may be a weakness here with case sensitivity, but I haven’t tested that out yet. By default if no filter value is entered, all “house” results are returned. As soon as a value is entered, it’s checked against the logic in the “if” statement. Room for improvement would be checking against a list of all known houses (perhaps an array, if there’s not too many values in the real application) for valid matches and if none are found returning an “Invalid House” result.

This worked in my brief tests:
 

view: test {
derived_table: {
sql: select 'gryffindor' as house
union all
select 'ravenclaw' as house
union all
select 'hufflepuff' as house
union all
select 'slytherin' as house ;;
}

parameter: house_picker {
label: "Choose Your House"
type: string
}

dimension: dynamic_house {
label: "House"
sql: {% if house_picker._parameter_value contains 'harry' %} 'gryffindor'
{% elsif house_picker._parameter_value contains 'luna' %} 'ravenclaw'
{% elsif house_picker._parameter_value contains 'ernie' %} 'hufflepuff'
{% elsif house_picker._parameter_value contains 'draco' %} 'slytherin'
{% else %} ${TABLE}.house
{% endif %}
;;
}
}