Question

Using liquid parameters to filter multiple values

  • 16 April 2019
  • 8 replies
  • 1566 views

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”.




8 replies

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 - 

 

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"}
}

 

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 ?

Perfect. Thanks Kyle and Ian!

Userlevel 1

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.

Userlevel 6
Badge

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

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?

Userlevel 1

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

Reply