I have created couple of .QVDs and one .QVW for my Project. .QVW has two years worth of data from Apr-08 to Apr-10. According to client's requirement they need 2 years worth of data every end of the month. No more, No less.
At the begining of month june-10, I'll have to hand them a new .QVW with may-08 to may-10 data. In order to do this What I can do is entirely reload the .QVW with every bit of data.
But since the data from Apr-08 to Apr-10 is already there in existing .QVW what I want to do is, drop Apr-08 and append may-10 to the .QVW file. Is that possible?
Please let me know if you need additional info to solve the problem.
Can I use binary load of first (apr-08 to apr-10) .QVW file? How will I filter the month of Apr-08 in this case? Is it possible to append the month of may-10 on the new .QVW file ?
please give me the rough syntax or point me in the right direction for, both dropping(apr-08) and appending(may-10) to a .QVW using binary load.
The way I done it is like this:
Extract every month of data in different QVDs. You will have 24 QVDs. Every new month you will extract a new one for the new month.
In QVW aplication you will have to load and concatenate only the last 24 QVDs
If you're also a calendar you could determine the period to be loaded in that calendar by limiting the calendar load to the period to be reported. When loading your data you could a where exists to determine whether the data is included in this period or not.
In case you require more data in your calendar you could create an indicator for your reporting period. Load these calander fields in a temp table and use the where exist against the temp table. After you have loaded your data you could drop your temp table.
Hope this helps.
Adding data is no problem. After doing a Binary Load of the QVW file just add the new QVD with the same column structure and it should be implicitly concatenated to the existing table.
The interesting part is eliminating a month from the QVW file in an efficient way. Doing a resident load of the table after the binary load with a where statement would work, but I don't know how much time it would take. Maybe loading a one column table with the month/year's you want to keep and they using the KEEP function would be another possibility to try.
The idea would be to do the following:
Load * //Same columns as Existing_Table