Help using Liquid values to insert a list of numerical values into a PDT

[kneebub]

Hello! I have a novel use of liquid values I need some help understanding. 

I am building an explore which runs off of a parameterized PDT allowing a stakeholder to input a comma-delimited list of numerical values (e.g. 1,2,3) which should feed into a liquid value I placed inside an IN statement filter. 

Here's an example of what the (sterilized) LookML code looks like:

First, I defined the input parameter in the view file:

parameter: categories_list

type: string

default_value: "1,2,3"

description: "Enter a comma-separated list of numerical categories."

Then, I pull this parameter into my PDT in the same view file using liquid values. See below:

SELECT ***

FROM ***

WHERE ***

AND my_table.my_category IN( {% parameter categories_list %} )

One very important note-- in my example above I am looking for matching values to the field my_category in the IN statement, but the values in my_category are numerical data types! The only way I could find to enable a list of ids to be entered was by using a string-type parameter, so when the liquid value gets inserted it looks like this: IN('1,2,3') which is obviously not syntactically correct. 

See the following error message:

So the main issue I'm having is how I can remove those single quotes Looker throws on my input value so that it is, in fact, a list of numerical values and not a string. When I change the parameter type to number it only allows me to input a single number when I need it to accept a list of numbers. Any sort of manual removal of the single quotations (for example, REPLACE('1,2,3', '''', '') doesn't even seem to affect the parameter.

Any help appreciated! 

[GavinW]

Have you tried setting the parameter to type: unquoted ?

[kneebub]

Yes! Unquoted-type parameters cannot accept a comma-delimited list (no commas allowed, unfortunately). 

[kneebub · Accepted solution]

I figured this out! Definitely not an intended use case, but nevertheless it works. 

I was thinking about this all wrong-- instead of trying to force the string parameter into a different data type so that it would work with the IN() statement, I had to accept that the data type has to be a string and change how I interacted with it instead.

Instead of using an IN() statement, I switched to a CONTAINS()

CONTAINS(CONCAT(',', {% parameter categories_list ','), CONCAT(',', CAST(my_table.my_field AS STRING), ','))

But why add all the commas? Well in cases where my input categories are in the teens or above (examples: 14, 25, 53), I'd also be creating matches on partial numbers in the string- so we'd be bringing in data associated with the categories 1, 4, 2, 5, and 3 as well instead of just 14, 25, or 53. Adding the commas around each distinct number in my input list tricks SQL into treating them as discrete items. 

This does in fact work as a way to enable users to provide a comma-delimited list of numerical values via an explore which are inserted into the underlying PDT!