Question

Using liquid parameters to filter multiple values

  • 16 April 2019
  • 7 replies
  • 756 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”.




7 replies

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

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 6
Badge

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

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.

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

 

Reply