Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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
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;