Excluding dimensional values

[sigurdfjeld]

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 · Accepted solution]

May be this:

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

[sigurdfjeld]

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

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

This also works