topic Re: Set expression using "expression search" only evaluates in $ context in App Development

Set expression using "expression search" only evaluates in $ context

Sat, 16 Nov 2024 10:18:22 GMT

Here's my test-data

But the user could provide their own data;

LOAD * INLINE [

year, id , col1, col2

2012, 2012-1, 3, 5

2013, 2013-1, 4, 4

2014, 2014-1, 5, 3

2012, 2012-2, 6, 2

2013, 2013-2, 7, 1

2014, 2014-2, 8, 0

];

I want to get the id of ALL rows where col1<=col2

But when I use this Set expression (with {1} for ALL), and the Set expression filter relies on an "expression search"...

=CONCAT({1<id={'=col1<=col2'}>}DISTINCT id, '|')

It doesn't seem to evaluate in the {1} all context where I want it to ignore the user's selections.

i.e. it seems to evaluate in the {$} context.

So instead of returning 2012-1, 2013-1

It returns 2012-1 if the user's clicked 2012, 2013-1 if the user's clicked 2013, and only returns both 2012-1, 2013-1 (which is what I want), if the user deselects the year field.

See the same in attached Qlikview.

Is this behavior mentioned in the documentation? Am I making a mistake? Note the workarounds I considered, can you give me any alternatives?

The Expression Search

Re: Set expression using "expression search" only evaluates in $ context

swuehl — Sat, 22 Oct 2016 21:21:45 GMT

Try

=CONCAT({1<id={'=Only({1} col1<=col2)'}>}DISTINCT id, '|')

remember that the advanced search is basically creating a hypercube with id being the dimension and the search string being the expression.

Re: Set expression using "expression search" only evaluates in $ context

Sun, 23 Oct 2016 07:00:17 GMT

"advanced search" -- is that the same thing as Henric's "expression search" mentioned in his blog?
"creating a hypercube" means that the outer {1} I used for the containing Set expression is not applied -- it's creating a totally new hypercube and applying the True/False results as a filter.
"search string being the expression" -- and since's it's a "plain old expression", while ONLY might be implied in my expression (that's true, right? ONLY is implied?) , when you use ONLY() explicitly, then you can introduce a new set expression, which reintroduces the "ALL" requirement with {1}: ONLY({1}...).

I experimented with =ONLY({1}col1)<=ONLY({1}col2), I did not get good results, but maybe I made a typo... or in fact they are very different things and ONLY({1}col1) is NULL, and same for ONLY({1}col2), and so the "advanced search"/"expression search" could not evaluate, and so Qlikview produces zero results.

Cool. Thanks, Stefan. I am assuming your answer works (not testing now), and marking it correct.

Re: Set expression using "expression search" only evaluates in $ context

Sun, 23 Oct 2016 07:06:48 GMT

Interesting cross-post about philosophy of "flag field" versus "evaluate on-the-fly [in set expression]". Re: Set modifier problem with empty column . Difference almost negligible, maybe I should consider a flag field for clarity, which is more important for me as I work with a team on this Qlikview.

Re: Set expression using "expression search" only evaluates in $ context

swuehl — Sun, 23 Oct 2016 10:29:23 GMT

1) In my understanding, 'advanced search' and 'expression search' are describing the same feature in this context (though 'expression search' is actually the better naming, giving a hint of how it works)

2) Correct, that's how I understand it.

3) Correct

I think =ONLY({1}col1)<=ONLY({1}col2) should return the same results as my suggested search expression, if you don't get results, than maybe your dimension used in the hypercube (id) is not granular enough to show unambiguous expression results?