Creating a ranking with Script

[brunolelli87]

Hello guys,

I'm trying to create a ranking with the following script:

Valendo:
Load
KKEY, 
[P/L],
[ROE]
Resident Final;

Join (Valendo)
Load
KKEY,
STCK3,
DVPG
Resident Cotações;

TabelaComparacao:
noconcatenate Load
KKEY,
[P/L],
[ROE],
STCK3,
DVPG
Resident Valendo;
Drop Table Valendo;


TabelaPL:
Load 
STCK3,
[P/L] as [P/L4],
DVPG  as DVPG4,

AutoNumber([P/L], DVPG) as RankPL
Resident TabelaComparacao Order By DVPG, [P/L] desc;

It's working good, and I'm getting the right information back as you can see below:

Now I'm trying to create another table, to rank the field ROE,  with the same index DVPG.

How can I do that?
It seems the counter is not being reset.

[sunny_talwar · Accepted solution]

Try this may be

Valendo:
LOAD KKEY, 
     [P/L],
     [ROE]
Resident Final;

Join (Valendo)
LOAD KKEY,
     STCK3,
     DVPG
Resident Cotações;

TabelaComparacao:
LOAD KKEY,
     [P/L],
     [ROE],
     STCK3,
     DVPG,
     If(DVPG = Previous(DVPG), RangeSum(Peek('RankPL'), 1), 1) as RankPL
Resident Valendo
Order By DVPG, [P/L] desc;

DROP Table Valendo;

FinalTabelaComparacao:
LOAD *,
     If(DVPG = Previous(DVPG), RangeSum(Peek('RankPL'), 1), 1) as RankROE
Resident TabelaComparacao
Order By DVPG, [ROE] desc;

DROP Table TabelaComparacao;

[brunolelli87]

Thank you so much!

It's working perfeclty