Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to filter all items where a name does NOT exist in group 10.
Name | Group | Boolean |
---|---|---|
John | 10 | |
John | 20 | |
John | 30 | |
Jack | 20 | |
Jack | 30 | |
Rob | 20 |
When loading this 'source' document as-is, it would result in 6 records.
I would like to filter all result for group 20 and 30 , but exclude results that also exist in group 10. If name = 10, always skip this name from result.
The result should only contain Jack and Rob : John does exist in group 10 and should not be added.
If I filter on group 20,30 , 'John' would still show up as he exist in groups 20 and 30. Could this be done via the GUI ?
I know I could add advanced filtering via the Ctrl+shift+ F, but only on one column-value. Would this be a solution? My final result should also depend on some other boolean values.
Well..
You could use something like this:
=aggr(if(index(Concat(Group,';'), '10')=0,Name), Name)
However, it looks for every instance of 10 so if you have a group called 110 it would filter out that name too
(you can cirumvent this by concatenating start and end characters to the Group field in the Concat() function.)
My new attempt was to add a chart and within that chart use a calculated column (min (Group) ) . This will for each name show if a name is in an group 10 (from my example) Only john is in group '10' . by selection 'the rest' i have my query result....
In my example this 10 could be extracted because it was the lowest; in other cases your example would do Johannes.
I did find the usefull informaion via your reply on the aggr function via http://community.qlik.com/message/70711#70711
Difficult solution to eliminate all accurances of a name if a name is 'In an group' .