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.