Using Rank and Aggr in script

[#1]

Hi Qlik experts,

I am having a difficult time making an app for showcasing Qlik Sense on some sports data (yeah i know this is the QlikView forum, but this is a script-related question, so as i have understood it shouldn't make a difference on the syntax?).

Consider the following table:

Name	Position	Team	Season	Measure	Rank
XXX	WR	111	2012	15	WR1
ZZZ	WR	111	2012	13	WR2
ZZZ	WR	111	2013	16	WR1
PPP	WR	222	2012	15	WR1
NNN	WR	222	2012	14	WR2

The "Rank" table is what i want to reproduce and add to my datamodel in Qlik - all the other data is already in my dataset.

So basically i want to rank the players by [Measure] grouped by the Position, Team and Season (and prefixed with the Position).

Can someone show me an example or give me a hint or something? That would be really neat.

Thanks

//René

[Nicole-Smith · Accepted solution]

Original:

LOAD * INLINE [

    Name, Position, Team, Season, Measure

    XXX, WR, 111, 2012, 15

    ZZZ, WR, 111, 2012, 13

    ZZZ, WR, 111, 2013, 16

    PPP, WR, 222, 2012, 15

    NNN, WR, 222, 2012, 14

];

Temp:

LOAD *,

  if(Position=Previous(Position) and Team=Previous(Team) and Season=Previous(Season), PEEK('Rank')+1, 1) as Rank

RESIDENT Original

ORDER BY Position, Team, Season, Measure DESC;

Final:

NOCONCATENATE LOAD Name, Position, Team, Season, Measure, Position & Rank as Rank

RESIDENT Temp;

DROP TABLES Original, Temp;

Example file is attached.

[maxgro]

source:

LOAD Name,

    Position,

    Team,

    Season,

    Measure,

    Rank,

    AutoNumber(Season & '|' & Position & '|' & Team) as Group

FROM

[https://community.qlik.com/thread/175200]

(html, codepage is 1252, embedded labels, table is @1);

final:

load

  Group,

  Name,

    Position,

    Team,

    Season,

    Measure,

    if(Group<>Peek(Group), 1, Peek(NewRankTmp)+1) as NewRankTmp,

    Position & if(Group<>Peek(Group), 1, Peek(NewRankTmp)+1) as NewRank 

Resident source

order by Group, Measure desc;   

DROP Table source;

DROP Field NewRankTmp, Group;