QVD with Incremental Load

Tue, 11 Jun 2013 10:27:08 GMT

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

topic QVD with Incremental Load in QlikView

QVD with Incremental Load