Using liquid parameters to filter multiple values

akash3
Participant I

This is a two part question -

  • Is there a way to use a parameter field to allow for filtering multiple values? Currently, the filter shows up as a text box that only allows a single value to be input. Am I missing something obvious here? If this isn’t possible, are there commonly used workarounds?

  • Can filtering on parameters be done with operators other than “Equal To”? The specific one we’d like to use is “Starts With”.

0 8 6,974
8 REPLIES 8

kpmartin87
Participant III

Hi Akash,

For a parameter I don’t think you can do multiple values there. You could do this with values delimited with commas. For example you can have a parameter for Geographic State. You could then add the values ‘NY’,‘CA’,‘TX’ into said parameter. The statement below should work:

CASE
WHEN ${state} in ({% parameter geographic_state %}) then ‘In-State’
ELSE ‘Out State’
END

If you want to do “Starts With” you could use my above but change it to ‘N%’

CASE
WHEN ${state} ILIKE ({% parameter geographic_state %}) then ‘In-State’
ELSE ‘Out State’
END

Hopefully this helps or gives some ideas

Best,
Kyle

akash3
Participant I

Hey Kyle - thanks for that. I tried the first method, using the IN operator in the query, but the values are of the string datatype and hence don’t quite work in the query as expected. For example, if we filter the parameter for `CA,NY,TX’, it gets inserted into query as a single string - WHERE state IN (‘CA,NY,TX’) instead of (‘CA’,‘NY’,‘TX’).

Is there a simple workaround to this?

IanT
Participant V

Use a filter and templates filters, unless there is a real need for a param

kpmartin87
Participant III

If you need the parameter you can always make the type unquoted and in your parameter make it ‘CA’,‘NY’,‘TX’. Using it as a filter would help solve this as well.

akash3
Participant I

Perfect. Thanks Kyle and Ian!

Hi, I am having same issue and when I use the type unquoted and pass the multiple value like ‘usa’,‘aus’ I get the error -

Filter on unquoted field keyword_page_report_pop.country_segmentA must contain only underscores, numbers, letters, ‘.’, or ‘$’.

can u please help ?

If you use BigQuery, try this

sql:
CASE
WHEN ${state} IN UNNEST(SPLIT( {% parameter geographic_state %} )) THEN ‘In-State’
ELSE ‘Out State’
END
;;

parameter: geographic_state {
type: string
allowed_value: {value: "NY,CA,TX"}
}

Solution that works for me with Snowflake - 

  parameter: state_multi_select {
type: string
}

dimension: multi_select_states_vs_others {
type: string
label: "Selected States vs. All Others"
sql: iff(array_contains(to_variant(${state_field}), split({% parameter state_multi_select %}, '.')),'Selected States','All Others');;
}

The state entries in the parameter must be delimited with a period ‘.’ so that Looker doesn’t return an error and Snowflake can still split them into an array.

Then convert your state dimension to a variant so snowflake accepts it in the array_contains function. 

In my use case, ${state_field} dimension contains the two letter abbreviation for the state and each record in my table has only 1 state associated with it.

Here is a screenshot showing the expected result with TX.FL.VA in the parameter - 

4e515dd5-241b-4912-bbce-44db7f64a5be.png
Top Labels in this Space
Top Solution Authors