Categories
OAC: How to Perform Expression Filtering with Attribute 'Like' Across a List of Values?
Hello everyone,
I need to perform a filter expression in OAC DV like this: attribute LIKE '%' || (list of values) || '%'.
List of values comes from a parameter used as dashboard filter.
Looking at the logical query behind it, it seems that if I select multiple values in the dashboard filter, the query has been interpreted as follows:
- attribute LIKE '%' ||('value1', 'value2', ...)|| '%'
Obviously, it is semantically incorrect and it does not work. Is there a way to make system interpret the filter expression as:
- attribute LIKE '%'||'value1'||'%' OR attribute LIKE '%'||'value2'||'%' OR ... ?
The number of selected values in the dashboard filter can change so i cannot write the query myself with a fixed number of OR operators.
Answers
-
The number of selected values in the dashboard filter can change so i cannot write the query myself with a fixed number of OR operators.
This is why there isn't an operator in SQL doing what you ask.
LIKE works on a single value, IN working on a list of values but it's full match values, not part of their value.
I don't think there is a checkbox or anything else making this for you, just because even in SQL there isn't a single operator doing that work.
I would say you should review your requirement to avoid having to perform that kind of query (which is also going to be performing poorly on the database.
0 -
Thanks Gianni for your answer.
Unfortunately, the clients is used to old biapps analytics where there was an option called 'contains any' that can interpret the query as i asked in the question. And so, here it comes the requirement to reproduce something like that in OAC DV.
Thanks,
Mattia
0 -
Hello @User_D8ODJ , do you know up to how many distinct values a user may enter in the filter clause ?
0 -
Hello Philippe, i don't know the maximum number of values that a user may enter. But, if we can make it work or we can solve it just by limiting to 15/20 distinct values, I'm open to any suggestion.
0 -
I can't think of a graceful workaround for this in current version. Maybe by creating multiple parameters and letting user set distinct values in each variable. That would allow you to sequence the filter syntax with correct 'OR' operators, and would be a robust solution. However that's a somewhat heavier functional experience for the user.
0 -
Did you click on "Convert this filter to SQL" in that popup?
"Products"."P1 Product" LIKE concat(concat('%',@{your_variable}{'%'}),'%')Which obviously isn't going to work because it doesn't mean the same. I imagine it would work better with a static list of values because that can be turned into a proper SQL expression (the list of values is known before to even try to generate a query, but I didn't look at how that operator generates queries, I generally stay away from it).
I suspect what Philippe has in mind by asking how many values (hoping an answer of 2-3 instead of 15-20) is that you can write a very long and bad looking string transformation set of operations on your variable to extract each component of the list of values and make it perform a <column> LIKE '%value 1%' OR <column> LIKE '%value 2%' etc.
The thing is that you would need to write the logic for each element of the list and that's why the number of 15-20 means an ugly, bad performing, expression (because you have to write an expression handling 20 values.
0
