1 Reply Latest reply: Mar 7, 2016 5:46 AM by Henric Cronström RSS

    How to count entered and removed id based on date?

    Silambarasan M

      Input table

      Id.      Date

      1        1-jan

      2.        1-jan

      1.         2-jan

      2.          2-jan

      3.         2-jan

      1.       3-jan

      3.       3-jan

      3.       4-jan

      5.        4-jan

       

       

       

      Expected output

       

      Using pivot table count number of new ID  enter everyday and number of existing ID remove everyday

       

      Note: if same day one new ID enter and existing ID remove then display count value as 1-1.

       

      Month.     Day.   1.     2.      3.        4

       

      Jan.                     2.      1.      1.       1-1

       

       

       

      tresesco hic QlikView Forums & Resources Scripting New to QlikView

        • Re: How to count entered and removed id based on date?
          Henric Cronström

          If you use the following script, you will flag first and last date of an ID.

           

          TmpData1:
          Load * Inline
          [Id,Date
          1,2015-01-01
          2,2015-01-01
          1,2015-01-02
          2,2015-01-02
          3,2015-01-02
          1,2015-01-03
          3,2015-01-03
          3,2015-01-04
          5,2015-01-04]
          ;

          TmpData2:
          Load *, If(Id<>Peek(Id),True()) as IsFirstDay
          Resident TmpData1 Order By Id, Date;

          Data:
          Load *, If(Id<>Peek(Id),True()) as IsLastDay
          Resident TmpData2 Order By Id, Date Desc;

          Drop Table TmpData1, TmpData2;

           

          With this you can calculate new and removed IDs. The first expression shows new IDs, and the second expression shows the number of disappeared IDs.

           

          Pivot.png

           

          HIC