topic Re: Auto Ranking for each unique ID in App Development

Auto Ranking for each unique ID

samuel_lin — Thu, 25 Jan 2018 14:50:58 GMT

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

Re: Auto Ranking for each unique ID

sunny_talwar — Thu, 25 Jan 2018 14:58:37 GMT

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;

Re: Auto Ranking for each unique ID

samuel_lin — Thu, 10 May 2018 18:31:23 GMT

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

Re: Auto Ranking for each unique ID

sunny_talwar — Thu, 10 May 2018 18:56:06 GMT

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