Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

QVD with Incremental Load

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

Community Browser