topic Re: Using Rank and Aggr in script in QlikView

Using Rank and Aggr in script

Tue, 04 Aug 2015 13:44:19 GMT

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é

Re: Using Rank and Aggr in script

Nicole-Smith — Tue, 04 Aug 2015 17:23:08 GMT

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.

Re: Using Rank and Aggr in script

maxgro — Tue, 04 Aug 2015 18:30:24 GMT

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;