Looping and generating new records

[#1]

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

[swuehl · Accepted solution]

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;

[#3]

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

[#4]

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;