accumulate monthly snapshots into one .qvd

[Anonymous]

Hi,

what am I trying to achieve?

A snapshot of our members at the end of every month.  I want to accumulate the data into one file so that I can then upload this file into qlikview for users to search past months.

So far I have written a script that loads the data into a qvd file but how do I know set it up to  concatenate/accumulate next months results into the same file?

So far I've loaded the following:-

Load
DateUpdated
,date(SnapshotDate,'DD/MM/YYYY') as SnapshotDate
,Year(addmonths(date(SnapshotDate,'DD/MM/YYYY'),6)) AS FiscalYear
,Month(date(SnapshotDate,'DD/MM/YYYY')) as Month
,num(Month(date(SnapshotDate,'DD/MM/YYYY'))) as MonthNumber
,'Q' & Ceil(Month (addmonths(date(SnapshotDate,'DD/MM/YYYY'),6))/3) AS FiscalQuarter
,AccountNo
,AccountName ;

SQL.............;

STORE MemberMonthlySnapshots INTO $(varFileName);

any help appreciated

[tresesco]

Check this thread: Re: Incremental Loading of Multiple CSV Files to QVDs to Mthly QVDs to Master QVD

[Anonymous]

Hi Tresesco,

I read it but I'm a little confused.  I am using a .qvw to generate the monthly .qvd files.  Can I accumulate into the same .qvd file or do I need to accumulate into a .qvw table?

Cheers

[Peter_Cammaert]

To get you started, consider this approach. These are general steps, to be translated into script code if you think they will fit your requirements.

1. If the Historical QVD exists, load it into memory but exclude all rows for the current month (SnapShotMonthYear).
2. From the DBMS, load all rows belonging to the current snapshot and tag them with SnapShotMonthYear = CurrentMonth+CurrentYear. Concatenate this set to the historical table.
3. Save the table to the historical QVD (thereby overwriting the previous version!)
4. If the current QVW doesn't contain a UI, drop the table and leave.

The advantages of this strategy are that the operation is pretty efficient, and that you can repeat the snapshot-taking without accumulating too much data. Every time you repeat the reload, the new snapshot for the current month will overwrite the previous snapshot of the current month.

BTW SnapShotMonthYear is a field I am adding to your data to tag the rows of your snapshot-QVD.

Best,

Peter

[Anonymous]

so its a qvw table I am updating not the qvd file.  doh me!  I thought I could append to an existing qvd.

thanks Peter.

[Peter_Cammaert]

Do you really think your own reply is helpful to this discussion or to other community members visiting this thread? If not sure, please read this: Qlik Community Tip: Marking Replies as Correct or Helpful

Much like an Excel spreadsheet, a QVD file cannot be appended to. You'll have to load it into RAM, append to the table in memory and rewrite the source qvd to get the same effect. Actually this is much quicker than adding to an existing file, because if you load and save QVDs in a straightforward fashion, you maintain their extreme R/W-speed.

Best,

Peter

[Anonymous]

Peter,

that was an accident.  I meant to place helpful against your response 🙂

[Peter_Cammaert]

You can always unmark a helpful if you think that would make the discussion less awkward. The entry in the Actions menu will help you.