20 Replies Latest reply: Aug 14, 2017 11:37 AM by Mike Lees RSS

    Create Cumulative  Count of Records in script by date

    Mike Lees

      So I've been asked to provide cumulative count of our products we have installed by date. I need to to make sure we include date even if we haven't installed anything on that that. I already have a calendar QVD I can leverage.

       

      This needs to be a script because I need to then link this new table to a different data set

       

      Example of installed list

       

         

      AccountInstallDateSerial Number
      1234515/07/201710
      1234516/07/201711
      1234519/07/201712
      1234525//07/201713
      1234529/07/201714
      1234502/08/201715
      1234505/08/201716
      1234510/08/201717
      1234512/08/201718

       

      Example of Output

       

         

      DateAccountCount
      01/07/2017123450
      02/07/2017123450
      03/07/2017123450
      04/07/2017123450
      05/07/2017123450
      06/07/2017123450
      07/07/2017123450
      08/07/2017123450
      09/07/2017123450
      10/07/2017123450
      11/07/2017123450
      12/07/2017123450
      13/07/2017123450
      14/07/2017123450
      15/07/2017123451
      16/07/2017123452
      17/07/2017123452
      18/07/2017123452
      19/07/2017123453
      20/07/2017123453
      21/07/2017123453
      22/07/2017123453
      23/07/2017123453
      24/07/2017123453
      25/07/2017123454
      26/07/2017123454
      27/07/2017123454
      28/07/2017123454
      29/07/2017123455
      30/07/2017123455
      31/07/2017123455
      01/08/2017123455
      02/08/2017123456
      03/08/2017123456
      04/08/2017123456
      05/08/2017123457
      06/08/2017123457
      07/08/2017123457
      08/08/2017123457
      09/08/2017123457
      10/08/2017123458
      11/08/2017123458
      12/08/2017123459
      13/08/2017123459

       

      Any ideas on how to produce this would be great

        • Re: Create Cumulative  Count of Records in script by date
          Robin Hausdörfer

          1) count your installed list, I guess grouped InstallDate like that:

          TEMP:

          load

          count(InstallDate) as Count

          Installdate

          resident YOURTABLE

          group by InstallDate;

           

          2) do a left join : YOURCALENDAR.qvd left join TEMP via Keyfield InstallDate

           

          3) sum up everything

           

          FINAL:

          InstallDate,

          Rangesum(Peek('CumulativeCount'), Count) as CumulativeCount

          resident YOURJOINEDTABLE;

          • Re: Create Cumulative  Count of Records in script by date
            Devarasu R

            Hi,

            Can you try like below?


            Data:

            LOAD * INLINE [

            Account, InstallDate, Serial_Number

                12345, 15/07/2017, 10

                12345, 16/07/2017, 11

                12345, 19/07/2017, 12

                12345, 25//07/2017, 13

                12345, 29/07/2017, 14

                12345, 02/08/2017, 15

                12345, 05/08/2017, 16

                12345, 10/08/2017, 17

                12345, 12/08/2017, 18

            ];

             

            Fact:

            LOAD

                Account,

                InstallDate,

                Count(Serial_Number) + If(IsNull(Peek('Cumulative')), 0, Peek('Cumulative')) AS Cumulative

            Resident Data

            GROUP BY Account, InstallDate;

             

            DROP TABLE Data;

             

            Thanks,Deva

            • Re: Create Cumulative  Count of Records in script by date
              Antonio Mancini

              Hi Mike,

              TableA:
              LOAD * Inline [
              Account, InstallDate, Serial Number
              12345, 15/07/2017, 10
              12345, 16/07/2017, 11
              12345, 19/07/2017, 12
              12345, 25/07/2017, 13
              12345, 29/07/2017, 14
              12345, 02/08/2017, 15
              12345, 05/08/2017, 16
              12345, 10/08/2017, 17
              12345, 12/08/2017, 18]
              ;
              LOAD Min(InstallDate) as MinDate,Max(InstallDate) as MaxDate
              Resident TableA;
              LET vMinDate = Peek('MinDate',RecNo());
              LET vMaxDate = Peek('MaxDate',RecNo());
              TableB:
              LOAD DISTINCT Account,Date(MonthStart($(vMinDate))+IterNo()-1) as InstallDate
              Resident TableA
              While Date(MonthStart($(vMinDate))+IterNo()-1) <= $(vMaxDate);
              Left Join (TableB) LOAD * Resident TableA;
              LOAD *,If(Account=Peek(Account) and Len(Trim([Serial Number])) > 0,
              RangeSum(Peek(Cumulative),1),RangeSum(Peek(Cumulative),0)) as Cumulative
              Resident TableB Order By Account,InstallDate;
              Drop Table
              TableA,TableB;

              Regards,

              Antonio

              • Re: Create Cumulative  Count of Records in script by date
                kushal chawda

                Another approach could be by using fastest way of calculating Min and max Date

                 

                 

                Data:

                LOAD * Inline [

                Account, InstallDate, Serial Number

                12345, 15/07/2017, 10

                12345, 16/07/2017, 11

                12345, 19/07/2017, 12

                12345, 25/07/2017, 13

                12345, 29/07/2017, 14

                12345, 02/08/2017, 15

                12345, 05/08/2017, 16

                12345, 10/08/2017, 17

                12345, 12/08/2017, 18];

                 

                 

                T1:

                LOAD date(MinDate+IterNo()-1) as InstallDate

                While MinDate+IterNo()-1<=MaxDate;

                LOAD monthstart(min(Date)) as MinDate,

                          max(Date) as MaxDate;

                LOAD FieldValue('InstallDate',RecNo()) as Date

                AutoGenerate FieldValueCount('InstallDate');

                 

                 

                Left Join(T1)

                LOAD Distinct Account

                Resident Data;

                 

                 

                Left Join(T1)

                LOAD *

                Resident Data;

                 

                 

                DROP Table Data;

                 

                 

                Final:

                LOAD *,

                         alt(if(AutoNumber([Serial Number],Account) >0,AutoNumber([Serial Number],Account) ,Peek('Cumulative')),0) as Cumulative

                Resident T1

                Order by Account,InstallDate;

                 

                 

                DROP Table T1;

                  • Re: Create Cumulative  Count of Records in script by date
                    kushal chawda

                    or even more simpler

                     

                     

                    Data:

                    LOAD *, AutoNumber([Serial Number],Account) as Serial Inline [

                    Account, InstallDate, Serial Number

                    12345, 15/07/2017, 10

                    12345, 16/07/2017, 11

                    12345, 19/07/2017, 12

                    12345, 25/07/2017, 13

                    12345, 29/07/2017, 14

                    12345, 02/08/2017, 15

                    12345, 05/08/2017, 16

                    12345, 10/08/2017, 17

                    12345, 12/08/2017, 18];

                     

                    T1:

                    LOAD date(MinDate+IterNo()-1) as InstallDate

                    While MinDate+IterNo()-1<=MaxDate;

                    LOAD monthstart(min(Date)) as MinDate,

                              max(Date) as MaxDate;

                    LOAD FieldValue('InstallDate',RecNo()) as Date

                    AutoGenerate FieldValueCount('InstallDate');

                     

                    Left Join(T1)

                    LOAD Distinct Account

                    Resident Data;

                     

                    Left Join(T1)

                    LOAD *

                    Resident Data;

                     

                    DROP Table Data;

                     

                    Final:

                    LOAD *,

                             alt(if(Serial>0,Serial,Peek('Cumulative')),0) as Cumulative

                    Resident T1

                    Order by Account,InstallDate;

                     

                    DROP Table T1;

                      • Re: Create Cumulative  Count of Records in script by date
                        Mike Lees

                        Hi Krushal

                         

                        This was very useful and I almost have this working, the issue I was when I add an additional Account the Cumulative starts at 1 instead 0

                         

                        Any ideas

                          • Re: Create Cumulative  Count of Records in script by date
                            kushal chawda

                            Hi Mike,

                             

                            This could happen because let's say for Account B, you have serial number starts from installdate 1-08-2017 which is minimum date then your Cummulative will start with one as I have used autonumber. Do you want the it to start from 0?

                              • Re: Create Cumulative  Count of Records in script by date
                                Mike Lees

                                I need it to start at 0 for every account until it finds the first serial number ordered by date

                                  • Re: Create Cumulative  Count of Records in script by date
                                    kushal chawda

                                    try this

                                     

                                    Data:

                                    LOAD * Inline [

                                    Account, InstallDate, Serial Number

                                    12345, 15/07/2017, 10

                                    12345, 16/07/2017, 11

                                    12345, 19/07/2017, 12

                                    12345, 25/07/2017, 13

                                    12345, 29/07/2017, 14

                                    12345, 02/08/2017, 15

                                    12345, 05/08/2017, 16

                                    12345, 10/08/2017, 17

                                    12345, 12/08/2017, 18];

                                     

                                    T1:

                                    LOAD date(MinDate+IterNo()-1) as InstallDate

                                    While MinDate+IterNo()-1<=MaxDate;

                                    LOAD monthstart(min(Date)) as MinDate,

                                              max(Date) as MaxDate;

                                    LOAD FieldValue('InstallDate',RecNo()) as Date

                                    AutoGenerate FieldValueCount('InstallDate');

                                     

                                    Left Join(T1)

                                    LOAD Distinct Account

                                    Resident Data;

                                     

                                    Left Join(T1)

                                    LOAD *

                                    Resident Data;

                                     

                                    DROP Table Data;

                                     

                                    Final:

                                    LOAD *,

                                              alt(if(Serial=0,Peek('Cumulative'),Serial),0) as Cumulative;

                                    LOAD *,

                                             autonumber(alt([Serial Number],'NA'),Account)-1 as Serial

                                    Resident T1

                                    Order by Account,InstallDate;

                                     

                                    DROP Table T1;

                                      • Re: Create Cumulative  Count of Records in script by date
                                        Mike Lees

                                        I'm still struggling

                                         

                                        example account 1234 has a date 10/08/2015

                                         

                                        when I select account 12345 years 2015 upto 15/07/2017 so Cumulative value of 1 however it needs to be 0

                                         

                                        Data:

                                        LOAD * Inline [

                                        Account, InstallDate, Serial Number

                                        12345, 15/07/2017, 10

                                        12345, 16/07/2017, 11

                                        12345, 19/07/2017, 12

                                        12345, 25/07/2017, 13

                                        12345, 29/07/2017, 14

                                        12345, 02/08/2017, 15

                                        12345, 05/08/2017, 16

                                        12345, 10/08/2017, 17

                                        12345, 12/08/2017, 18

                                        1234, 10/8/2015, 5]
                                        ;



                                        T1:

                                        LOAD date(MinDate+IterNo()-1) as InstallDate

                                        While MinDate+IterNo()-1<=MaxDate;

                                        LOAD monthstart(min(Date)) as MinDate,

                                        max(Date) as MaxDate;

                                        LOAD FieldValue('InstallDate',RecNo()) as Date

                                        AutoGenerate FieldValueCount('InstallDate');



                                        Left Join(T1)

                                        LOAD Distinct Account

                                        Resident Data;



                                        Left Join(T1)

                                        LOAD *

                                        Resident Data;



                                        DROP Table Data;



                                        Final:

                                        LOAD *,

                                        alt(if(Serial=0,Peek('Cumulative'),Serial),0) as Cumulative;

                                        LOAD *,

                                        autonumber(alt([Serial Number],'NA'),Account)-1 as Serial

                                        Resident T1

                                        Order by Account,InstallDate;



                                        DROP Table T1;