QVD files

A QVD (QlikView Data) file is a file containing a table of data exported from Qlik Sense or QlikView. QVD is a native Qlik format and can only be written to and read by Qlik Sense or QlikView. The file format is optimized for speed when reading data from a Qlik Sense script but it is still very compact. Reading data from a QVD file is typically 10-100 times faster than reading from other data sources.

QVD files can be read in two modes: standard (fast) and optimized (faster). The selected mode is determined automatically by the Qlik Sense script engine. Optimized mode can be utilized only when all loaded fields are read without any transformations (formulas acting upon the fields), although renaming of fields is allowed. A Where clause causing Qlik Sense to unpack the records will also disable the optimized load.

A QVD file holds exactly one data table and consists of three parts:

• An XML header (in UTF-8 char set) describing the fields in the table, the layout of the subsequent information and some other metadata.
• Symbol tables in a byte-stuffed format.
• Actual table data in a bit-stuffed format.

QVD files can be used for many purposes. Four major uses can be easily identified. More than one may apply in any given situation:

• Increasing data load speed

By buffering non-changing or slowly-changing blocks of input data in QVD files, script execution becomes considerably faster for large data sets.

• Decreasing load on database servers

The amount of data fetched from external data sources can also be greatly reduced. This reduces the workload on external databases and network traffic. Furthermore, when several Qlik Sense scripts share the same data, it is only necessary to load it once from the source database into a QVD file. The other applications can make use of the same data through this QVD file.

• Consolidating data from multiple Qlik Sense applications.

With the Binary script statement it is possible to load data from only one single Qlik Sense application into another one, but with QVD files a Qlik Sense script can combine data from any number of Qlik Sense applications. This opens up possibilities for applications consolidating similar data from different business units etc.

• Incremental load

In many common cases the QVD functionality can be used for facilitating incremental load by exclusively loading new records from a growing database.

Creating QVD files

A QVD file can be created in two ways:

• Explicit creation and naming using the Store command in the Qlik Sense script.

State in the script that a previously-read table, or part thereof, is to be exported to an explicitly-named file at a location of your choice.

• Automatic creation and maintenance from script.

By preceding a load or select statement with the Buffer prefix, Qlik Sense will automatically create a QVD file, which under certain conditions, can be used instead of the original data source when reloading data.

There is no difference between the resulting QVD files, with regard to reading speed.

Store

This script function creates an an explicitly named QVD, CSV, or txt file.

Syntax:  

The statement can only export fields from one data table. If fields from several tables are to be exported, an explicit join must be made previously in the script to create the data table that should be exported.

The text values are exported to the CSV file in UTF-8 format. A delimiter can be specified, see LOAD. The store statement to a CSV file does not support BIFF export.

Examples:

Do the following:

1. Open the Advanced Scripting Tutorial app.
2. Click the Product script section.
3. Add the following to the end of the script:

Store * from Product into [lib://DataFiles/ProductData.qvd](qvd);

Your script should look like this:

CrossTable(Month, Sales)
LOAD
    Product,
    "Jan 2014",
    "Feb 2014",
    "Mar 2014",
    "Apr 2014",
    "May 2014"
FROM [lib://DataFiles/Product.xlsx] 
(ooxml, embedded labels, table is Product);

Store * from Product into [lib://DataFiles/ProductData.qvd](qvd);

4. Click Load data.

The Product.qvd file should now be in the list of files.

This data file is the result of the Crosstable script and is a three-column table, one column for each category (Product, Month, Sales). This data file could now be used to replace the entire Product script section.

Reading data from QVD files

A QVD file can be read into or accessed by Qlik Sense by the following methods:

• Loading a QVD file as an explicit data source. QVD files can be referenced by a load statement in the Qlik Sense script just like any other type of text files (csv, fix, dif, biff, and so on).

Examples:

LOAD * from [lib://DataFiles/xyz.qvd] (qvd); LOAD fieldname1, fieldname2 from [lib://DataFiles/xyz.qvd] (qvd); LOAD fieldname1 as newfieldname1, fieldname2 as newfieldname2 from [lib://DataFiles/xyz.qvd](qvd);

• Automatic loading of buffered QVD files. When using the buffer prefix on load or select statements, no explicit statements for reading are necessary. Qlik Sense will determine the extent to which it will use data from the QVD file as opposed to acquiring data using the original LOAD or SELECT statement.
• Accessing QVD files from the script. A number of script functions (all beginning with QVD) can be used for retrieving various information on the data found in the XML header of a QVD file.

Do the following:

1. Comment out the entire script in the Product script section.
2. Enter the following script:

Load * from [lib://DataFiles/ProductData.qvd](qvd);			

3. Click Load data.

The data is loaded from the QVD file.

Data load progress window

Buffer

QVD files can be created and maintained automatically via the Buffer prefix. This prefix can be used on most LOAD and SELECT statements in script. It indicates that QVD files are used to cache/buffer the result of the statement.

Syntax:  

If no option is used, the QVD buffer created by the first execution of the script will be used indefinitely.

Example:  

stale [after] amount [(days | hours)]

Amount is a number specifying the time period. Decimals may be used. The unit is assumed to be days if omitted.

The stale after option is typically used with database sources where there is no simple timestamp on the original data. A stale after clause simply states a time period from the creation time of the QVD buffer after which it will no longer be considered valid. Before that time the QVD buffer will be used as source for data and after that the original data source will be used. The QVD buffer file will then automatically be updated and a new period starts.

Example:  

Incremental

The incremental option enables the ability to read only part of an underlying file. The previous size of the file is stored in the XML header in the QVD file. This is particularly useful with log files. All records loaded at a previous occasion are read from the QVD file whereas the following new records are read from the original source and finally an updated QVD file is created.

Note that the incremental option can only be used with LOAD statements and text files and that incremental load cannot be used where old data is changed or deleted.

Example:  

QVD buffers will normally be removed when no longer referenced anywhere throughout a complete script execution in the app that created it or when the app that created it no longer exists. The Store statement should be used if you wish to retain the contents of the buffer as a QVD or CSVfile.

Do the following:

1. Create a new app and give it a name.
2. Add a new script section in the Data load editor.

3. Under DataFiles in the right menu, click Select data.

4. Upload and then select Cutlery.xlsx.
5. In the Select data from window, click Insert script.
6. Comment out the fields in the load statement, and change the load statement to the following:

Buffer LOAD *

Your script should look like this:

Buffer LOAD *
//     "date",
//     item,
//     quantity
FROM [lib://DataFiles/Cutlery.xlsx]
(ooxml, embedded labels, table is Sheet1);

7. Click Load data.

The first time that you load data, it is loaded from Cutlery.xlsx.

Data load progress window


The Buffer statement also creates a QVD file and stores it in Qlik Sense. In a cloud deployment, it is stored in a directory in the cloud.

8. Click Load data again.
9. This time the data is loaded from the QVD file created by the Buffer statement when you loaded the data for the first time.
Data load progress window

Thank you!

Now you have finished this tutorial, and hopefully you have gained some more knowledge about scripting in Qlik Sense. Please visit our website for more information on the further training available.