topic Re: Mark highest values in QlikView

Mark highest values

Anonymous — Tue, 01 Apr 2014 14:34:51 GMT

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?

Re: Mark highest values

alexandros17 — Tue, 01 Apr 2014 14:37:52 GMT

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

Re: Mark highest values

its_anandrjs — Tue, 01 Apr 2014 14:49:44 GMT

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

Re: Mark highest values

MK_QSL — Tue, 01 Apr 2014 14:53:34 GMT

Aggr(Max(Score),UserID)

Re: Mark highest values

Anonymous — Tue, 01 Apr 2014 15:05:25 GMT

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