topic Re: Need to calculate Dense Rank in QlikView

Need to calculate Dense Rank

Tue, 24 Jun 2014 21:40:19 GMT

Hello,

I am making a sports result ranking and need to calculate a dense rank (eg. 1,2,3,4,4,5,6,7,8,8,8,9) based on value from two columns (Poeng and Innertreff).

My table looks like this:

Navn	Poeng	Innertreff
Anders	75	9
Øyvind	70	14
Ole Petter	70	14
Anders	70	10
Jo	66	12
Arne	62	20
Hans	59	19
Thor	59	19

I want to add a column with DenseRank like this:

DenseRank	Navn	Poeng	Innertreff
1	Anders	75	9
2	Øyvind	70	14
2	Ole Petter	70	14
3	Anders	70	10
4	Jo	66	12
5	Arne	62	20
6	Hans	59	19
6	Thor	59	19

Anyone who can help me with this?

Re: Need to calculate Dense Rank

Nicole-Smith — Tue, 24 Jun 2014 21:50:15 GMT

This should do the trick:

data:

LOAD * INLINE [

    Navn,Poeng,Innertreff

    Anders,75,9

    Øyvind,70,14

    Ole Petter,70,14

    Anders,70,10

    Jo,66,12

    Arne,62,20

    Hans,59,19

    Thor,59,19

];

final:

LOAD *, if(previous(Innertreff)=Innertreff,peek('DenseRank'),rangesum(peek('DenseRank'),1)) as DenseRank

RESIDENT data

ORDER BY Poeng desc;

drop table data;

Re: Need to calculate Dense Rank

Tue, 24 Jun 2014 21:56:17 GMT

Robert,

you need it in load script or in UI?

regards

Darek

Re: Need to calculate Dense Rank

Tue, 24 Jun 2014 22:00:21 GMT

Hi, it would be best to have it in UI, as I will use various selections and I want to rank different selections. There is gonna be a lot of data added, so inline loading of the table isn't practical.

Thanks

Re: Need to calculate Dense Rank

Tue, 24 Jun 2014 22:12:29 GMT

Do you expect that both fields are integers?

Re: Need to calculate Dense Rank

Tue, 24 Jun 2014 22:21:32 GMT

yes, they are

Re: Need to calculate Dense Rank

Tue, 24 Jun 2014 22:23:46 GMT

i thik, something like:

rank(Poeng+max(total Innertreeff) + Innertreeff)

should give expected results.

regards

Darek

Re: Need to calculate Dense Rank

Tue, 24 Jun 2014 22:44:37 GMT

Robert,

in fact use:

dimension: Navn

expression: rowno()

and sort your result by expression:

Poeng+max(total Innertreeff) + Innertreeff

regards

Darek

Re: Need to calculate Dense Rank

Tue, 24 Jun 2014 23:02:44 GMT

sorry:

expression like:

rank(Poeng+ Innertreeff / max(total Innertreeff) )

will be better.

Re: Need to calculate Dense Rank

Wed, 25 Jun 2014 07:40:12 GMT

After a bit of trying, I have come to the conclusion that I have to resolve this in the load script.

I've tried using Nicoles code, but I get an error when I Reload data.

Field not found

final:

LOAD *, if(previous(Innertreff)=Innertreff,peek('Plass'),rangesum(peek('Plass'),1)) as Plass

RESIDENT data

ORDER BY Poeng desc, Innertreff desc

This is the load script:

data:

LOAD * INLINE [

Dato

Stevne

Dato

Navn

Forening

Poeng

Innertreff

Merknad

];

final:

LOAD *, if(previous(Innertreff)=Innertreff,peek('Plass'),rangesum(peek('Plass'),1)) as Plass

RESIDENT data

ORDER BY Poeng desc, Innertreff desc;

drop table data;

Re: Need to calculate Dense Rank

Wed, 25 Jun 2014 11:13:05 GMT

Hello again,

After working with this a bit, I see now that I need it to be in the Load script to make it work.

Do you have any ideas?

Re: Need to calculate Dense Rank

Wed, 25 Jun 2014 11:55:24 GMT

why dont you just try:

rank(sum(Poeng+Innertreff))

it is a lil work around but it should give you what you need.

regards,

Re: Need to calculate Dense Rank

Wed, 25 Jun 2014 12:23:35 GMT

Hi,

That would not work, because the list will first be ranked by 'Poeng', THEN by 'Innertreff'

If you just put those together, then 30 and 10 would outrank 35 and 1.

Re: Need to calculate Dense Rank

Nicole-Smith — Wed, 25 Jun 2014 16:00:45 GMT

data:

LOAD

Dato,

Stevne,

Navn,

Forening,

Poeng,

Innertreff,

Merknad

FROM YourExcelFile.xlsx;

final:

LOAD *, if(previous(Innertreff)=Innertreff,peek('Plass'),rangesum(peek('Plass'),1)) as Plass

RESIDENT data

ORDER BY Poeng desc, Innertreff desc;

drop table data;

Re: Need to calculate Dense Rank

Wed, 25 Jun 2014 19:18:40 GMT

The load script now looks like this and runs without errors:

data:

LOAD

     Ovelse,

     Poeng,

     Innertreff

FROM

(biff, embedded labels);

final:

LOAD *, if(previous(Poeng)=Poeng,peek('Plass1'),rangesum(peek('Plass1'),1)) as Plass1

RESIDENT data

ORDER BY Poeng desc, Innertreff desc;

drop table data;

There are two problems with this.

1. I need the ranking grouped by Ovelse

2. The ranking only ranks Poeng, I need it to FIRST rank by Poeng, THEN rank by Innertreff and if both matched, then the rank should be the same.

Re: Need to calculate Dense Rank

Nicole-Smith — Wed, 25 Jun 2014 19:22:17 GMT

I didn't realize you changed the ranking code from my original post...

final:

LOAD *, if(previous(Innertreff)=Innertreff,peek('Plass1'),rangesum(peek('Plass1'),1)) as Plass1

RESIDENT data

ORDER BY Poeng desc;

Re: Need to calculate Dense Rank

Wed, 25 Jun 2014 19:26:19 GMT

I got the ranking correct now, by changing the loadscript like this:

data:

LOAD

     Ovelse,

     Poeng,

     Innertreff

FROM

(biff, embedded labels);

final:

LOAD *, if(previous(Poeng)=Poeng and previous(Innertreff)=Innertreff,peek('Plass1'),rangesum(peek('Plass1'),1)) as Plass1

RESIDENT data

ORDER BY Poeng desc, Innertreff desc;

drop table data;

The only thing I need now is to group the ranking by Ovelse (need a rank within each ovelse)

Re: Need to calculate Dense Rank

Nicole-Smith — Wed, 25 Jun 2014 19:33:22 GMT

final:

LOAD *, if(previous(Ovelse)<>Ovelse,1, if(previous(Poeng)=Poeng and previous(Innertreff)=Innertreff,peek('Plass1'),rangesum(peek('Plass1'),1))) as Plass1

RESIDENT data

ORDER BY Ovelse asc, Poeng desc, Innertreff desc;

drop table data;

Re: Need to calculate Dense Rank

Wed, 25 Jun 2014 19:47:56 GMT

That did not change the ranking.

The Problem, I think, is that you cannot use previous with 'Ovelse'.

I need to group the data by Ovelse (there are 4 different possible values in Ovelse).

Then rank internally within each 'Ovelse'