Process of Incremental Load in QVD QlikView

Creating QVDs (QlikView Data Files)

Creating a QVD file or files is important because, when working with large sets of data, it is these native file types that can be read at literally millions of rows per second.

QVD is a QlikView format and can only be written to and read by QlikView. A single QVD file can store a single table and is created in the load script in a QVW file. It plays a crucial part in the majority of QlikView implementations.

Learn how to use QlikView, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll in Free QlikView Training Demo!

A QVD file consists of three parts:

• XML header to describe the fields in the table, the layout of the subsequent information, and other meta-data.

• Symbol tables in a byte stuffed format.

• Actual table data in a bit-stuffed format.

A QVD file stores data in one QlikView document and it is created using the script editor available in the QlikView document. QVDs are most commonly created during the execution of the QlikView load script using the STORE command. Each QVD is equal to one logic table (named and created in the script), such as in this example:

STORE sales into
C: qlik viewQVDssales.qvd(qvd);

A variable name can be configured previous to this STORE statement that defines the QVD storage location. The variable can be created in the following format:

Set v save QVD=
C:qlik viewQVDs;

Once created, the variable can be used as in the following STORE statement. The dollar sign  ($) is called dollar sign expansion in qlik view, and it instructs qlik view to evaluate the v save QVD variable. In this case, to return the location of the saved QVD directory path.

STORE sales into
$ (v save QVD) sales . qvd (qvd);

QVDs must be initially created using a full load, but can be maintained and updated either by doing another full load or performing an incremental load. An incremental load can be a much faster load (in some cases, radically decreasing the load times depending on the data sources and data set) and can add new records, or update or delete them (in the data source) in the QVD-depending on how the load script is configured.

Related Page: How Debugging Works In QlikView Script Debugger

Full loads of QVDs

Full loads are always the starting point for the initial QVD creation, and they may be used at any time that is appropriate for you. Full loads include the following tasks:

• Initial load of QVD of data from a data source

• Total refresh of data on a scheduled basis (for example, weekly or monthly)

• Small data sources with fast loads of QVDs

• Fallback load scenario after an incremental load fails

The full-reload process is relatively simple, usually without a date filter, but it can have any selection criteria via the where clause, as shown in the following example:

Table name:
SQL SELECT ID,…
FROM table-name;
Store table name into
$ (v save QVD) QVD name. Qvd   (qvd);

In the preceding script, the table name is the logical table in qlik view, v save QVD is a variable that is set up with the saved QVD directory as in the preceding section, and QVD name is any QVD name you want to employ.

Related Page: QlikView Scripting & QlikView Hidden Scripts

Here is a full load example from an excel spreadsheet, where we have named the orders. Xlsx spreadsheet (and from the orders tab in the spreadsheet), and store the data as a QVD named sales.qvd, in the location defined by the v save QVD variable:

Sales: LOAD  invoice ID,
Product ID, customer ID, sales,
Date-order FROM$ (v source data) ord
Ers. Xlsx (ooxml,  embedded
Labels, a table is orders);
STORE sales into
$ (v save QVD) sales. Qvd (qvd);

QVD Incremental Loads 

Incremental loads are defined as loading only new or updated records from the database into an established QVD. Incremental loads are useful because they run much faster than full loads, particularly for larger data sets from databases.

At a higher level, incremental loads follow these general steps:

1. Create a QVD via a full load.
2. Periodically run the incremental load by loading the new or updated data from the database into a table in the qlik view script.
3. Load in and concatenate the old data currently in the existing QVD to the newly created table in the script.
4. Create a new QVD file containing the old and new data, and repeat this for each QVD that you need to update incrementally.
The following is an example of an incremental load that uses an excel file for the data source and handles (not deletes) incremental loads for updates and new data. In this particular case, we are using the embedded qlik view QVD creates a date function (this is a timestamp updated by qlik view each time the QVD is created or updated). In the excel file, there are two tabs, orders and more-orders. The documentation is in the code (using comment notation/// * for clarity and allowing you to use the full code in a script).

Related Page: How To Optimize QlikView Application User Interface - QlikView

We need to set several variables first so, we will set a variable for the directory location of the source data files (v source data) and a directory location for the QVD file that we had created and saved (v save QVD). Then we create a variable that is the date of creation that is embedded into the QVD from qlik view (the embedded date is QVD create time; the new variable we create from that is V QVD create date). We do this so that we can reformat it with the date function and call this variable v updated date.

*/
Set v source data=
‘c: qlik viewdata files’;
Set v save QVD=
‘c:qlik viewQVDs’;
Let v QVD create date=
QVD create time (‘$ (v save QVD) sales
.qvd’);
Let v up dated date=
Date (‘$ (v QVD create date)’);
/*

The following code block is the first full data load. After it loads once, we can comment it out, so that only the incremental loads run after the QVD is established. Other ways to accomplish this may be by checking to see if the QVD already exists and doing a full load only if it does not exist. This full load extracts all data from the orders. Xlsx work sheet and stores it as a QVD file (sales. Qvd). The locations of the excel QVD files that are to be saved are represented by the variables we set earlier.

// START OF FULL LOAD
Sales:
LOAD invoice ID,
     Product ID,
    Customer ID,
Sales,
Updated
FROM
$ (v source data) broker sales. Xlsx
(ooxml, embedded labels, table
Is orders);
STORE sales into
$ (v save QVD) brokers sales. Qvd(qvd)
;
//END OF FULL LOAD
/*

Next, we load the updated or new records from the source data. Note that we are pulling this data from a new tab in excel (more-orders). This tab has the new and updated records. We use the no concatenate function because we don’t want anything to concatenate yet.

Next, we load the updated or new records from the source data. Note that we are pulling this data from a new tab in excel (more-orders). This tab has the new and updated records. We use the no concatenate function because we don’t want anything to concatenate yet.

*/
Incremental:
No concatenate
LOAD invoice ID,
     Product ID,
     Customer ID,
    Sales,
/*

Using the following date function sets the data in the updated column to explicitly appear as a date:
*/
  Date (updated) as updated
FROM $ (v source data) orders. Xlsx (ooxml, embedded labels, table is more-orders)
/*

The next line selects only those records with updated or new records-this is where the column in the data source excel sheet, updated, is later than the variable value we created from the QVD create date in the QVD. This does not address deletions.

Frequently Asked QlikView Interview Questions & Answers

Note:

Note that in a live database environment, the time between the querying of the database and updating of the QVD could lead to some rows being lost. In this case, you could rewrite the date stamp part of the script for the update time or persist the date before the data load is executed. This example is fine for reading from the excel files with less robust usage and updates.

*/
Where updated   >=
$ (v updated date);
/*

Take the information from the QVD and concatenate it with the new table we have just made (consisting of new updates or additions), then store it back into the broker sales. Qvd

*/
Concatenate (incremental)
LOAD  invoice ID,
      PRODUCT ID,
      Customer ID,
      Sales,
/*

Using the following date function, it sets the data in the updated column to explicitly appear as a date. Note that because we are changing the format of the field in this QVD that is read, the data read will be in a standard mode. One solution would be to change the field format using the date functions when the original QVD file is created, and read the field without any function. This would get you to the optimized load.

*/
Date (updated) as updated
FROM
$ (v save QVD) broker sales. Qvd
(qvd)
/*

We use the where not exists clause because we don’t want to pull in any rows from the existing QVD with the same invoice-ID values as the rows we just pulled in from the data source. Note that it is critical that the ID used here is unique to each row.

*/
Where not exists (invoice ID);
/*

Then we store the new concatenated (old, new, and updated data) table in the broker sales. Qvd file. Ultimately, the incremental table is dropped.

*/
STORE incremental into
$ (v save QVD) broker sales. Qvd(qvd);
Drop table incremental;

Managing QVDs is one of the most important parts of any QlikView application project. It can be used to provide a logical data layer and hence it can act as a data warehouse in absence of one (one of the big benefits of Qlikview).

Related Page: Color Alerts And Calculated Colors In QlikView Scripting - QlikView

Creating QVDs helps to decrease the database and network load. Incremental load removes the deleted records. As a result of this, your application consumes less space and requires less load time.

End notes on Advantages of using QVDs:

QVD files offer many advantages to your QlikView applications, including the following:

• Faster load time: Reading data from QVD is 10-100 times faster than other sources i.e. decreasing load time.

• Less load on databases and networks: Once data is exported in QVDs, you don’t need to connect with an external database again. It reduces the workload on external databases and network.

• Incremental loads of QVDs: Incremental load (loading only new/ modified detail of a database) can be done only by using QVDs. It reduces the load time significantly as compared to complete load.

• Consolidating data from multiple data sources and databases: Multilayer QVDs are used to create a with robust model, when data is fetched from multiple data sources.

Explore QlikView Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!