1 Reply Latest reply: Jan 30, 2017 8:25 AM by Sunny Talwar RSS

    Hi Team, I have  a flat file and I need to create some aggregate fields from it using Script

    Siddharth Sharma

      Hi Team,

       

      Below is raw file,

         

      Id DateStatus
      11/5/2017Open
      11/6/2017Open
      11/8/2017Open
      11/9/2017Closed
      11/10/2017Open
      11/11/2017Open
      11/12/2017Open
      11/14/2017Cosed
      11/15/2017Open
      11/16/2017Open
      11/17/2017Open
      11/20/2017Open

       

       

      I need to create aggregate table that has some new columns and one column with Counter

      I need Result like

       

           

      IdOpen DateClosed DateStatus#Cases
      11/5/20171/9/2017Still Open1st Time
      11/10/20171/14/2017Still Open2nd Time
      11/15/2017 Still Open

      3rd Time

       

      SO this table is actually telling us that Id was 1st Closed on 9th Jan then it again opened on 10th Jan and then 2nd time it closed on 14th Jan but then on 15th it again reopened,  As this Id is still open hence we have Status still open,and I also have counter variable($ CAses) that says for how many time this Id opened

       

      I need these variable using Qlikview script.

       

      Thanks in Advance

       

      Regards

      Sid

        • Re: Hi Team, I have  a flat file and I need to create some aggregate fields from it using Script
          Sunny Talwar

          Try this:

           

          Table:

          LOAD * INLINE [

              Id, Date, Status

              1, 1/5/2017, Open

              1, 1/6/2017, Open

              1, 1/8/2017, Open

              1, 1/9/2017, Closed

              1, 1/10/2017, Open

              1, 1/11/2017, Open

              1, 1/12/2017, Open

              1, 1/14/2017, Closed

              1, 1/15/2017, Open

              1, 1/16/2017, Open

              1, 1/17/2017, Open

              1, 1/20/2017, Open

          ];

           

          Table1:

          LOAD *,

            If(Id = Previous(Id),

            If(Status = Previous(Status), Peek('Order'), RangeSum(Peek('Order'), 1)), 1) as Order

          Resident Table

          Order By Id, Date;

           

          Table2:

          NoConcatenate

          LOAD Id,

            Date(Min(Date)) as Date,

            Status,

            Order

          Resident Table1

          Group By Id, Status, Order;

           

          Left Join (Table2)

          LOAD Id,

            FirstSortedValue(Status, -Date) as [Last Status]

          Resident Table2

          Group By Id;

           

          FinalTable:

          LOAD Id,

            Date as [Open Date],

            If([Last Status] = 'Open', 'Still Open', 'Closed') as Status,

            RowNo() & ' Time' as [#Cases]

          Resident Table2

          Where Status = 'Open';

           

          Left Join (FinalTable)

          LOAD Id,

            Date as [Closed Date],

            If([Last Status] = 'Open', 'Still Open', 'Closed') as Status,

            RowNo() & ' Time' as [#Cases]

          Resident Table2

          Where Status = 'Closed';

           

          DROP Table Table, Table1, Table2;