Using LIKE operator and wildcards with Parameters

jwillcox
Participant I

Is there a way to use a free-text parameter in a fuzzy “LIKE” statement including wildcards?

For example - I have a simple text string parameter called reason_code_filter that accepts any value:

parameter: reason_code_filter {
type: string
}

And a measure set up to use that parameter as a condition

measure: sum_reason_code {
type: sum
sql: CASE WHEN reason_code LIKE {% parameter reason_code_filter %} THEN 1 ELSE 0 END ;;
}

This works great if a user enters the full exact value of “reason_code” in the filter field, but I’m looking to set it up so that they can just enter part of the string. So I want to change the resulting SQL from

CASE WHEN reason_code LIKE ‘text’ THEN 1 ELSE 0

to

CASE WHEN reason_code LIKE ‘%text%’ THEN 1 ELSE 0

Is this something that’s possible to do? I’ve been playing around with trying to concatenate the wildcards into the sql field of the measure with no luck so far.

Any help is appreciated!

Edit: I should mention that I know I could achieve this by setting the expectation that if a user wants to do a partial match they have to put the wildcards in the filter field themselves, but looking to see if there’s a way to have it always run that way through the LookML.

1 2 9,103
2 REPLIES 2

Hi Josh- depending on your use case a templated filter might be useful since it’s a bit more flexible, see the difference between parameters and templated filters in this doc. For a templated filter, it could look something like:

type: sum
sql: CASE WHEN {% condition reason_code_filter %} reason_code {% endcondition %} THEN 1 ELSE 0 END ;;
}

If you decide you need to go the parameters route, it might be useful to try type: unquoted documented here: https://docs.looker.com/reference/field-reference/dimension-type-reference#unquoted. You could define the parameter by allowing only certain values, but with wildcards for the users to still have the flexibility. For parameter type: unquoted, something like:

type: sum
sql: CASE WHEN reason_code LIKE "%{% parameter reason_code_filter %}%" THEN 1 ELSE 0 END ;;
}

jwillcox
Participant I

Aha - templated filters are exactly what I was looking for. Thanks a bunch Desiree!

Top Labels in this Space
Top Solution Authors