Different ways of loading data into QlikView

Different Ways of Loading Data in to QlikView

Introduction:

There are different ways and methods to load data into Qlikview. Explained below are the methods of loading data into QlikView.

 

1. Loading Data from File:

This method is normal method of loading data into the Qlikview application.

Load data from files like Excel, MSDB, CSV, Txt files etc., can be done by creating ODBC and connecting to your database directly.

Example:

LOAD  A,  B

FROM

[C:\filename.xls] (biff, embedded labels, table is Data$);

 

2. Inline Load:

The second method of loading is Inline Load where the user can define their own data and load within Qlikview.

The Inline data can be defined in the inline Data Wizard.

Inline Data.

Example:

LOAD * INLINE [

A

B

C

];

 

3. Resident Load:

Loading data from already loaded Qlikview table is possible using Resident Load.

Transformations and Calculations can be performed in the resident load script.

Loading a existing field or a succeeding table is possible too.

Example:

 

Employees:

Select Empname,

HireDate,

Salary,

Incentives

From Employee;

 

Load Empname,

Month(HireDate),

Salary + Incentives as ‘GrossSalary’

Resident Employees;

 

4. Incremental Load:

Incremental load is used to load only the new or changed records.

It will be very helpful where a database is big.

This involves loading old data from the QVD and new records from the database and combine into single QVD.

Example:

 

Employee_Master:

LOAD EmpId,
EmpName,
DateOfBirth,
DateOfJoining,
Designation,
Department,
Salary,
Date(CreatedDate) as CreatedDate
FROM
$(RawPath)\Employee_Master.xls
(biff, embedded labels, table is [Worksheet$]) Where CreatedDate=’28-06-2014′;

Concatenate
LOAD EmpId,
EmpName,
DateOfBirth,
DateOfJoining,
Designation,
Department,
Salary,
CreatedDate
FROM
$(QVDPath)\Employee_Master.qvd
(qvd);

STORE Employee_Master into $(QVDPath)\Employee_Master.qvd (qvd);

DROP Table Employee_Master;

 

5. Binary Load:

Loading data from one qlikview file is called Binary Loading. The data model of one QVW file is copied from RAM to disk in 0s and 1s for another qvw file.

This Binary Load will be more usefull  when you want to enhance the already built qvw with the same metrices defined.

Example:

Binary c:\order.qvw;

 

6. Buffer Load:

If the keyword is prefixed before the load QVD files can be created automatically and stored in the Application Data folder. A BUFFER prefix on a LOAD or SQL statement creates and maintains an automatic QVD for that statement. Subsequent executions of the LOAD/SELECT statement will read from the QVD, avoiding another (slower) trip to the database. A read from QVD is generally 5-10 times faster than fetching from database. You can check the user preferences Folder for the location details. Buffer load helps to create the QVD files automatically.  The created QVD will be maintained as defined in  User defined Locations.

 

TranTab:

BUFFER LOAD
TranID,
Amount,
CustomerID,
etc…;
SQL SELECT * FROM TRANSACTIONS;

On first execution, the SELECT will fetch rows from the database and the resulting TranTab will be stored in a specially named QVD on the local machine. On subsequent reloads, TranTab will automatically be loaded from the local QVD.

If you make a change to the TranTab LOAD/SQL statement, QV Reload will detect the change and fetch from the database again and update the local QVD.

During script development it’s not uncommon to perform a reload several times. You can greatly reduce the duration of a script run by adding BUFFER to your statements. Any script changes/adds you make will automatically invalidate that buffer and re-fetch from the database.

Don’t forget to remove the BUFFER keyword before moving to production!