Question

Accessing nth value in templated filtering

  • 17 February 2017
  • 10 replies
  • 214 views

I’m trying to use the templated filtering and use { % condition filter_name %} table.column { % endcondition % }.

With multiple entries in the filter (let’s say they’re “a”,“b”), it will output "a" = table.column or "b" = table.column


Now the question is, is there any way to access the individual entries separately?


I can access the first value using { % parameter filter_name %}, however I’d like to be able to access them all individually. This way I’d be able to allow a user to group a column using their own keywords.


Is there any solution to this situation?


10 replies

Userlevel 4
Badge

Hey @nick, templated filters are always going to convert the user’s string to the corresponding filter value, so we won’t have much luck there.


It sounds like we may be better off trying to make this work in parameters. If I’m not mistaken, shouldn’t {% parameter filter_name %} return the entire string put in the filter box? So if the user inputs a,b, we will get the string “a,b” rather than just “a”.


Given this, you should be able to use a parsing function in SQL to get at the different values. For example, redshift has SPLIT_PART. I imagine something like

split_part({% parameter filter_name %}, ',' , 1)

to get the “a”, and

split_part({% parameter filter_name %}, ',' , 2)

to get the “b”.

Thanks @sam, this is almost perfect and will definitely help me create a satisfying explore. I assume this means that there isn’t a solution that would be able to handle any number of separate values?

Userlevel 4
Badge

Not that I can imagine @nick - but if you want to paste some more details on your use case, I can pass your feedback on to the product team!


Hey @sam! I stumbled across this post from eons ago, but I can’t seem to get the solution you’ve suggested to work…


I feel like what you’ve said above isn’t actually the case (it’s also very possible that I’m just not implementing it properly! 😅):



If I’m not mistaken, shouldn’t {% parameter filter_name %} return the entire string put in the filter box?



I’m trying to add a condition in my derived table using the count of the number of values added to the filter. (e.g. if the filter has is equal to: 'overdraft', 'signup' it would return 2). But, when I use {% parameter filter_name %} it only returns the first value from the set.


(I’ve attached some images of the filters, the rendered SQL, and the LookML below)


^In this example, you can see that both values are included in the templated filtering (i.e. WHERE ( tag IN ('overdraft', 'signup'))), but only the first value is included in the parameter (i.e. HAVING ARRAY_LENGTH(SPLIT('overdraft', ',')))


Any help here would be hugely appreciated! ❤

Userlevel 5
Badge

Hello @KatieH and welcome!

The issue is that a parameter can only have one value.


It’s a tricky one but I think I got it:


Try the following code:


having ARRAY_LENGTH([{{ _filters['VIEW_NAME.tag'] | split: "," | prepend: "'" | append: "" | join: "," | remove: "'[" | remove: "]" | escape | replace: """, "'" }}]) = COUNT(tag)


you’ll need to replace VIEW_NAME by your actual view name 🙂


It’s working on my end, let me know!


Screenshot bellow using snowflake:



It worked!! 🚀


I can’t believe I didn’t come across the liquid _filters variable in my hunt for a solution…what you’ve done makes total sense. Thanks you so much for your help @Cyril_MTL_Analytics!! ❤

Userlevel 5
Badge

Perfect!


@izzy could we open a ticket with the documentation team to update this page https://docs.looker.com/reference/liquid-variables?


It states the _filters['view_name.field_name'] variable is not working in the sql parameters but it actually is (see example in my previous post)


Userlevel 7
Badge +1

Request submitted, thanks!

@Cyril_MTL_Analytics @KatieH Hi there,


I was attempting to extract a string from a list of filter values and I got an error that “_filters” cannot be used in SQL. Are you still able to use it in SQL and if not, have you found a workaround by chance?


image image


Thanks!

Userlevel 5
Badge

It makes sense that you cannot use that liquid parameter in a sql statement since that dimension and the filters are generated at the same time.


The example above concerned the sql_having parameter.


I’m wondering what is your used case though.

Reply