A Qlikview QVD file is a binary data file for a single table without a layout or a security. QVD is a native data format used only by Qlikview to read and write data.
QVD file format typically has a small footprint and Qlikview application can read data 10 to 100 times faster from a QVD file than any other data sources. For an application with millions or billions of rows, Qlikview QVD file is the best way to store initial and incremental data loads for that table.
Having said that, the challenge remains to find a suitable partition such as a date field to create an incremental load. In the Qlikview tutorial for an incremental load, I have used a date field and used that date field to perform subsequent incremental data loads.
This process loads QVD file first and then performs a sort along with PEEK function to get the date field for the subsequent incremental load. For a large data set, sorting is time and resource consuming process. Not to mention, that I have to load QVD again to concatenate data with incremental rows from the source.
Indeed, necessity is the mother of creation. I stumbled upon a useful Qlikview function that came to my rescue. And that function is none other than QVDcreatetime. Normally this function is being used to check if QVD file exists or not. It is very helpful for a data load that depends on existence of a QVD. But I quickly found out that QVDcreatetime function also stores the date as part of the string.
I can store the date from this function into a variable and use that variable to perform subsequent incremental loads.
Step 1:
Create two variables: vQVDCreatetime to extract QVD create time stamp and vUpdateDate to store that string into another variable with a date format.
Step 2:
Remove step to load previously loaded QVD file to perform sort and to PEEK the latest date. Since we are using QVDcreatetime function, this step is unnecessary.
By using QVD createtime function, we have not only simplified incremental data load process but also made our Qlikview script more efficient by avoiding costly sort for a large data set.
Readers: Have you used QVD createtime function either to check if QVD file exists or to extract QVD time stamp?
Elsewhere:
EasyQlik Qviewer via EasyQlik.com
QVD Questions and Answers via Qlikview Notes
Ashutosh 5:00 am on July 5, 2013 Permalink
Hi Shilpan,
Nicely written article!
But I think that While using QVDCreateTime() will definitely save some time but issue could be there of time difference between geographic zones.
If it is to reduce the time taken in getting the max date of the QVD then we can take a variable and in it can store last successful incremental load date and next time can increment that date.
One more idea could be to store the incremental load data as a separate QVD also, that way you always have with you the last fetched data as well and on that taking max date will not take much time.
What will be your take on these approaches?
Regards,
Ashutosh
Shilpan 12:25 pm on July 5, 2013 Permalink
Hi Ashuthosh,
The approach that you have suggested should work as well. You can definitely use max date, but it may or may not take much time depending on the size of the previously loaded QVD file.
rahul 10:04 am on June 5, 2015 Permalink
Hello Sir
Very nice way you have done .
could you plz copy code to me my email : rahulqlik@gmail.com