3 Replies Latest reply: Jun 3, 2015 6:53 AM by James Green RSS

    Looping and generating new records

      Hello

       

      I've tried a few ways of doing this but I'd like some advice on the simplest method.

       

      I have a table loaded in my QV script (see attached excel file).

       

      For each unique PayrollNo there are 7 rows loaded with the Date field of each subsequent row being 1 calendar week after the previous. Different PayrollNo's can have different start dates but the same pattern of 6 subsequent weeks being loaded after remains.

       

      What I'd like to do is add additional records for each PayrollNo with the below rules...

       

      Take the latest loaded date for each PayrollNo and generate additional weekly records (previous Date + 7) and stops when Date >= 31/12/2015. Then repeat for next PayrollNo.

       

      All other fields apart from Date should stay the same apart from TrainingWeek which should increase in +1 increments (W8, W9, W10). WeeklySalesTarget should always be 2 for all additional week records created.

       

      Can someone please assist / advise?

       

      Thanks

      Adam

        • Re: Looping and generating new records
          Stefan Wühl

          Maybe something along this:

           

          INPUT:

          LOAD PayrollNo,

               ResourceNameOrig,

               pvTrainingStartDate,

               pvTrainingFullyProductive,

               TrainingWeek,

               TrainingStage,

               Date,

               WeeklySalesTarget

          FROM

          [.\DateLoop.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

          TMP:

          NOCONCATENATE

          LOAD PayrollNo,

            Date(max(Date)) as Date,

            FirstSortedValue(ResourceNameOrig, -Date) as ResourceNameOrig,

            FirstSortedValue(pvTrainingStartDate, -Date) as pvTrainingStartDate,

            FirstSortedValue(pvTrainingFullyProductive, -Date) as pvTrainingFullyProductive,

            mid(FirstSortedValue(TrainingWeek, -Date),2) as TrainingWeek,

            FirstSortedValue(TrainingStage, -Date) as TrainingStage,

            FirstSortedValue(WeeklySalesTarget, -Date) as WeeklySalesTarget

          RESIDENT INPUT

          GROUP BY PayrollNo

          ;

           

           

           

          DROP TABLE INPUT;

           

           

          RESULT:

          NOCONCATENATE

          LOAD

            PayrollNo,

            Date(Date+(iterno()-1)*7) as Date,

            ResourceNameOrig,

            pvTrainingStartDate,

            pvTrainingFullyProductive,

            'W' & (TrainingWeek+iterno()-1) as TrainingWeek,

            TrainingStage,

            WeeklySalesTarget

          RESIDENT TMP

          WHILE Date+(iterno()-1)*7 < MakeDate(2015,12,31);

           

           

          drop table TMP;

          • Re: Looping and generating new records

            Thanks Swuehl

             

            I still needed to retain the records from the INPUT table so I've just concatenated the RESULT records to this original table.

             

            And I also removed the "-1" from the Iterno() functions as I don't want W7 replicating.

             

            Thanks again.

            Adam

            • Re: Looping and generating new records
              James Green

              Hi Adam

               

              You can use a While loop for this.

               

              1. Find the latest 'Date' for each PayrollNo

              2. Use a While clause to generate each week between the latest 'Date' and the end date you have specified

              3. Concatenate the rows from 2. on to the original table

               

              LatestDate:

              LOAD

                PayrollNo

                ,ResourceNameOrig

                ,pvTrainingStartDate

                ,pvTrainingFullyProductive

                ,TrainingStage

                ,Max(Right(TrainingWeek,1)) as MaxTrainingWeek

                ,Max(Date) as MaxDate

              Resident

                DateLoopTable

              Group By

                PayrollNo

                ,ResourceNameOrig

                ,pvTrainingStartDate

                ,pvTrainingFullyProductive

                ,TrainingStage

              ;

               

               

              LET vEndDate = MakeDate(2015,12,31);

               

               

              RowsToConcatenate:

              LOAD

                PayrollNo

                ,ResourceNameOrig

                ,pvTrainingStartDate

                ,pvTrainingFullyProductive

                ,TrainingStage

                ,'W' & MaxTrainingWeek+IterNo() as TrainingWeek

                ,MaxDate+(IterNo()*7) as Date

              Resident

                LatestDate

              While

                MaxDate+(IterNo()*7)<=$(vEndDate)

              ;

               

              DROP Table LatestDate;

               

              Concatenate(DateLoopTable)

              LOAD

                PayrollNo

                ,ResourceNameOrig

                ,pvTrainingStartDate

                ,pvTrainingFullyProductive

                ,TrainingStage

                ,TrainingWeek

                ,Date

                ,0 as WeeklySalesTarget  // Or just leave as Null

              Resident

                RowsToConcatenate

              ;

               

               

              DROP Table RowsToConcatenate;