Mark highest values

[Anonymous]

Hello,

I need to be able to indicate in a table with UserIDs, channels, channelevents & channeleventscores what is the highest score per UserID.

Example:

UserID	Channel	Event	Score
abc123	Channel1	Event1	0.3
abc123	Channel1	Event2	0.4
abc123	Channel1	Event3	0.1
def456	Channel1	Event3	0.1
abc123	Channel2	Event1	0.5

I want to add an extra column to this table, indicating that row #3 en #4 are the higest scores for respectively abc123 & def456.

I have the following code at this moment:

UserActions:

LOAD ChannelEventUserID,

    Channel,

    UserID,

    Score,

    If(Peek(Channel) <> Channel OR Peek(UserID) <> UserID OR Peek(Score) <> Score,1,0) AS HighestActionFlag

Resident SomeOtherTable

Order by Channel, UserID, Score desc;

However, this doesn't seem to be working as expected.

Any idea what I could be doing wrong?

[alexandros17]

UserActions:

LOAD

     ChannelEventUserID,

     Channel,

     UserID,

     Score

Resident SomeOtherTable

Order by Channel, UserID, Score desc;

left join

LOAD

     UserID,

     Max(Score) as maxscore

Resident SomeOtherTable

group by UserId;

Hope it helps

[its_anandrjs]

Load your table like below

UserActions:

load * Inline

[

UserID, Channel, Event, Score

abc123, Channel1, Event1, 0.3

abc123, Channel1, Event2, 0.4

abc123, Channel1, Event3, 0.1

def456, Channel1, Event3, 0.1

abc123, Channel2, Event1, 0.5

];

Left Join

LOAD

     UserID,

     max(Score) as max

Resident UserActions

Group by UserID;

Hope this helps

[MK_QSL]

Aggr(Max(Score),UserID)

[Anonymous · Accepted solution]

Actually, I just found the solution myself...

This:

If(Peek(Channel) <> Channel OR Peek(UserID) <> UserID OR Peek(Score) <> Score,1,0) AS HighestActionFlag

Would need to become this:

If(Peek(Channel) <> Channel OR Peek(UserID) <> UserID,1,0) AS HighestActionFlag

Obviously, the "Score" part of the if function always returned a 1. By removing it, it returned only a 1 to the channel actions which were the highest...

Thanks all anyway for your help