Using Case statements in Filter in Looker Views

[nb_nb_nb]

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

[dbrinegar]

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 %}
    ;;
  }
}