0 Replies Latest reply: Aug 21, 2015 5:34 AM by Simon Hogg RSS

    Correct method to load QVD and update YTD (& combine with database extract)

    Simon Hogg

      I have a data load script which calculates the Year-to-Date at load-time;

       

      IF (DayNumberOfYear(InvoiceDate) <= DayNumberOfYear(Today())  THEN 'YTD' etc.

       

      But the load script is quite slow, loading many years of data.  I would like to load anything up to last year and save it in a QVD file and load it from disk.

       

      But obviously the YTD calculation will change each day.  How do I arrange the load script to make it work correctly?  Will the QVD store the YTD flag at the time of saviing the file or can it recalculate it after loading.

       

      Should I do something like:

      1. Load QVD file
      2. Load from Database
      3. Join Tables (Insert Into?)
      4. Apply Calculations

       

      or:

      1. Load QVD file
      2. Apply Calculations
      3. Load from Database
      4. Apply Calculations
      5. Join Tables (Insert Into?)

       

      And does it matter if I do it all in one long load module, or should I am to break it into multiple modules (or is that purely for presentation/readability?)

       

      (And, to add to the mix, I have a Master Calendar & Geographic attributes - where should I aim to join those to the loaded data?  At the moment (one load function, so one table) it works just fine.