Need to calculate Dense Rank

[#1]

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?

[Nicole-Smith]

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;

[#3]

Robert,

you need it in load script or in UI?

regards

Darek

[#4]

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

[#5]

Do you expect that both fields are integers?

[#6]

yes, they are

[#7]

i thik, something like:

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

should give expected results.

regards

Darek

[#8]

Robert,

in fact use:

dimension: Navn

expression: rowno()

and sort your result by expression:

Poeng+max(total Innertreeff) + Innertreeff

regards

Darek

[#9]

sorry:

expression like:

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

will be better.

[#10]

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;