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

[#1]

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

[swuehl · Accepted solution]

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.

[#3]

• "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.

[#4]

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.

[swuehl]

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?