Hi All,
I am after some help in regards to generating an Incremental LOAD QVD file
The Current script is as follows:
The script allows me to generate a
Brought Fwd value for Starters and Leavers at the end of a Period
the Number of Starters and Leavers in the Period
Carried Forward Balance for Starters and Leavers for the strat of the next Period
// First build a list of monthends from the dates Table:
// Volunteer Starters and Leavers
VolunteerTemp:
Load Distinct
Max(VolunteerCalendarMonthEnd) AS $VolunteerSLCalendarMonthEnd,
Min(VolunteerCalendarMonthStart) AS $VolunteerSLCalendarMonthStart,
//RangeMax(Date('01/01/2011','DD/MM/YYYY'),Min(VolunteerCalendarMonthStart)) AS VolunteerSLCalendarMonthStart,
VolunteerCalendarYear AS VolunteerSLCalendarYear,
VolunteerCalendarMonthName AS VolunteerSLCalendarMonthName
Resident VOLUNTEERCALENDAR
group by [VolunteerCalendarYear],[VolunteerCalendarMonthName];
Join (VolunteerTemp)
Load Distinct
VolunteerCode AS $VolunteerSLCode,
$VolunteerReference AS $VolunteerReference,
//$VolunteerGSReference AS $VolunteerSLGSReference,
VolunteerAnalysis AS $VolunteerSLAnalysis,
VolunteerStartDate AS $VolunteerSLStartDate,
VolunteerLeaveDate AS $VolunteerSLLeaveDate
Resident VOLUNTEER;
[VOLUNTEERSTARTERSnLEAVERS]:
Load *,
//VolunteerSLCalendarYear,
//VolunteerSLCalendarMonthName,
If((len($VolunteerSLLeaveDate)= 0 or $VolunteerSLLeaveDate > $VolunteerSLCalendarMonthEnd)
and $VolunteerSLStartDate <= $VolunteerSLCalendarMonthEnd,1,0) AS VolunteerSLCarriedFwd,
If($VolunteerSLLeaveDate >= $VolunteerSLCalendarMonthStart
and $VolunteerSLLeaveDate <= $VolunteerSLCalendarMonthEnd, 1,0) AS VolunteerLeavers,
If($VolunteerSLStartDate >= $VolunteerSLCalendarMonthStart
and $VolunteerSLStartDate <= $VolunteerSLCalendarMonthEnd, 1,0) AS VolunteerStarters,
If((len($VolunteerSLLeaveDate)= 0 or $VolunteerSLLeaveDate >= $VolunteerSLCalendarMonthStart)
and $VolunteerSLStartDate < $VolunteerSLCalendarMonthStart,1,0) AS VolunteerSLBroughtFwd,
If($VolunteerSLLeaveDate >= $VolunteerSLCalendarMonthStart nybo
and $VolunteerSLLeaveDate <= $VolunteerSLCalendarMonthEnd, 'VolunteerLeaver',
If($VolunteerSLStartDate >= $VolunteerSLCalendarMonthStart
and $VolunteerSLStartDate <= $VolunteerSLCalendarMonthEnd, 'VolunteerStarter',
If(len($VolunteerSLLeaveDate)= 0, 'Current Workforce','Left Previously'))) AS $VolunteerSLStatus
Resident VolunteerTemp
Order By $VolunteerSLCode,$VolunteerSLCalendarMonthEnd;
I would like to adjust the script so that I can save historical data in a QVD file and then append new values for the current Month.
E.g. As soon as the Month Roles over from the previous month to current month those values are saved to the QVD file and the script
will then reload that data from the QVD on the next reload and append any changes that take place during the current month.
Your help would be much appreciated.
Cheers
Martyn
