topic Excluding dimensional values in QlikView

Excluding dimensional values

sigurdfjeld — Mon, 18 Jan 2016 14:32:07 GMT

I have a dataset somewhat like this:

LOAD * INLINE [

UserID, Comment, Year, ID

1, a, 2015 ,1

2, a, 2015 ,2

3, a, 2015 ,3

4, a, 2015 ,4

2, b, 2015 ,5

3, b, 2015 ,6

1, c, 2013 ,7

2, c, 2014 ,8

3, c, 2014 ,9

4, c, 2015 ,10

2, d, 2013 ,11

3, d, 2013 ,12

4, d, 2013 ,13

1, a, 2016 ,14

];

I want to select all users that have comment 'a' but not comment 'b'. I can do the count like this:

count(DISTINCT {$<Comment = {'a'}, UserID = E({$<Comment = {'b'} >} UserID) >} UserID)

How would i go about printing a list of the users from this expression or making a calculated dimension?.

sunny_talwar — Mon, 18 Jan 2016 16:20:35 GMT

May be this:

=Aggr(Only({$<Comment = {'a'}, UserID = E({$<Comment = {'b'} >} UserID) >} UserID), UserID)

sigurdfjeld — Wed, 20 Jan 2016 09:21:40 GMT

=if(aggr(count(DISTINCT{$<Comment = {'a'}, UserID = E({$<Comment = {'b'} >}UserID) >} UserID)

, UserID)>= 1, UserID, null())

This also works