2 Replies Latest reply: Feb 8, 2016 11:53 PM by Jithu J RSS

    generating sequence for resident table

    Jithu J

      can anybody help me with the below scenario using Qlik Load Script.

       

      I have table with the below structure

       

          

      Ticket_nocreatedatechangeDatestatus
      11/27/20141/28/2014Modify
      11/27/20141/29/2014SUBMIT
      11/27/20142/2/2014CLOSED
      21/27/20141/28/2014Modify
      21/27/20141/29/2014SUBMIT
      32/2/20142/2/2014Modify
      32/2/20148/8/2014SUBMIT
      43/1/20143/3/2014Modify
      58/8/20148/8/2014Modify
      58/8/20148/10/2014SUBMIT
      68/9/20148/9/2014Modify
      68/9/20148/10/2014SUBMIT
      68/9/201411/11/2014CLOSED

       

      now I have created a resident table using qlik LOAD with the below structure (i.e. de-normalizing the above one )

       

      Resident Table :

      Ticket_noDateStatus
      11/27/2014Created
      11/28/2014Modify
      11/29/2014SUBMIT
      12/2/2014Closed
      21/27/2014Created
      21/28/2014Modify
      21/29/2014SUBMIT
      32/2/2014Created
      32/2/2014Modify
      38/8/2014SUBMIT
      43/1/2014Created
      43/1/2014Modify
      58/8/2014Created
      58/8/2014Modify
      510/8/2014SUBMIT
      68/9/2014Created
      68/9/2014Modify
      610/8/2014SUBMIT
      611/11/2014Closed

       

      Now I want to create a sequence for each ticket.... output should come like this

       

          

      sequenceSKU DateStatus
      111/27/2014Created
      211/28/2014Modify
      311/29/2014SUBMIT
      412/2/2014Closed
      121/27/2014Created
      221/28/2014Modify
      321/29/2014SUBMIT
      132/2/2014Created
      232/2/2014Modify
      338/8/2014SUBMIT
      143/1/2014Created
      243/1/2014Modify
      158/8/2014Created
      258/8/2014Modify
      3510/8/2014SUBMIT
      168/9/2014Created
      268/9/2014Modify
      3610/8/2014SUBMIT
      4611/11/2014Closed

       

      If I have the same Data in SQL then I can use the below query to the sequence

       

      select ROW_NUMBER() OVER (partition by ticket_no order by date asc)

               AS sequence,Ticket_no,Date,Status

           from table