topic Re: Advance filtering via GUI: exclude all possible values in QlikView

Advance filtering via GUI: exclude all possible values

andries_bos — Wed, 05 Dec 2012 10:46:39 GMT

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.

Re: Advance filtering via GUI: exclude all possible values

Anonymous — Wed, 05 Dec 2012 11:03:26 GMT

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.)

Re: Advance filtering via GUI: exclude all possible values

andries_bos — Wed, 05 Dec 2012 15:21:38 GMT

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' .