0 Replies Latest reply: Jun 11, 2013 6:27 AM by Martyn Alford RSS

    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