Introduction:
There are different ways and methods to load data into Qlikview. Explained below with examples and uses of methods of loading data into qlikview.
Types:
- Loading data from the file.
- Inline Load
- Resident Load
- Incremental Load
- Binary Load
- Buffer Load
- Partial Reload
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 .
InsertàLoad Dataà 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 helpfull where a database is big.
This involves loading old data from the QVD and new records from the database and combine into single QVD. The below picture depicts the dataflow.
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!
7. Partial Reload:
TRNS:
ADD LOAD Cust_Code, //Partial Reload
Prod_Code,
Quantity,
Revenue,
COGS,
Discount,
ED,
Date as InvoiceDate
FROM
[Trans.qvd]
(qvd);
Hope you like this tutorial. Stay connected with me.
Cheers..
Amit Kumar
Prince Tech Solutions
www.princetechsolutions.com
Email: [email protected]
YouTube Channel: Prince Tech Solutions
—————————————————————–
Online Training | Corporate Training | Consulting | Job Support