1 Reply Latest reply: Jan 25, 2018 9:58 AM by Sunny Talwar RSS

    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

        • Re: Auto Ranking for each unique ID
          Sunny Talwar

          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;