Auto Ranking for each unique ID

[samuel_lin]

Hi Qlikkers,

Say If I only have two columns, ID and [OrderDate],

ID     OrderDate

1     1/1/2018

1     1/2/2018

1     1/3/2018

2     1/1/2018

2      1/3/2018

and I want to add a column: OrderRank, which gives:

ID     OrderDate     OrderRank

1     1/1/2018          1

1     1/2/2018          2

1     1/3/2018          3

2     1/1/2018          1

2      1/3/2018          2

so it's a rank based on OrderDate and specific for each ID, is this possible with Qlik script?

Thanks,

Samuel

[sunny_talwar · Accepted solution]

May be try this

Table:

LOAD ID,

     OrderDate

FROM ....;

FinalTable:

LOAD ID,

     OrderDate,

     AutoNumber(RowNo(), ID) as Rank_Method1

     If(ID = Previous(ID), RangeSum(Peek('Rank_Method2'), 1), 1) as Rank_Method2

Resident Table

Order By ID, OrderDate;

DROP Table Table;

[samuel_lin]

Hi Sunny,

I lost working this project and just resumed today. Your solution works, I am just curious, when I spot checked, both Method works, but Method1 is much faster than Method2. Is this what you're expecting as well?

Thanks,

Samuel

[sunny_talwar]

I was not, I have seen/heard Method2 to perform significantly better than Method1.