Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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.
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.
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?