Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
lauracastagna
Creator II
Creator II

accumulate monthly snapshots into one .qvd

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

7 Replies
tresesco
MVP
MVP

lauracastagna
Creator II
Creator II
Author

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
Partner - Champion III
Partner - Champion III

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

lauracastagna
Creator II
Creator II
Author

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
Partner - Champion III
Partner - Champion III

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

lauracastagna
Creator II
Creator II
Author

Peter,

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.