Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using Rank and Aggr in script

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:

NamePositionTeamSeasonMeasureRank
XXXWR111201215WR1
ZZZWR111201213WR2
ZZZWR111201316WR1
PPPWR222201215WR1
NNNWR222201214WR2

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é

1 Solution

Accepted Solutions
Nicole-Smith

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.

View solution in original post

2 Replies
Nicole-Smith

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
MVP
MVP

1.png

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;