Incremental Load Statement in QlikView

Introduction

Large business data requires a very long reload time. In QlikView, an incremental load can reduce time. An incremental load loads new records or data or updates specific records of existing QVD files.

Features of incremental  load

  • It works only for insert and update.
  • It always loads initial data.
  • An incremental load gets the last data and loads dates.
  • It loads incremental data.
  • It concatenates the incremental data loaded.

There are the following three options for incremental data loads.

  • Insert only
  • Insert and update
  • Insert, update and delete

These are the basic operations of incremental data load.

Insert only

  • It can be used to load all new data from a data source.
  • It stores an entire table outside the QVD file.
  • It concatenates new data with QVD file data.

Insert and Update

  • It can be used to load all new and updated data from a data source.
  • It stores the entire table outside of the QVD file.
  • It concatenates new data with a load of only the missing QVD file data.

Insert, Update and Delete

  • It can be used to load all new and updated data from a data source.
  • It stores an entire table outside of the QVD file.
  • It concatenates new data with a load of only the missing QVD file data.
  • Inner joins all the primary keys from the data source.

So let's start working with incremental data loads.

Step 1: Open the QlikView application

In the first step you need to open the QlikView application then go to "File" -> "New"; then this window will be opened.

After clicking New option

Step 2: Open Edit Script

The second step is to open the edit script window from "File" -> "Edit Script".

window of edit script

  • Then this window will be opened.

    window

Step 3:Add tabs

Add 4 new tabs in the edit script for performing various operations of an incremental load.

tab

Step 4: Select main tab

In this tab make a connection from the SQL data source.

Step 5: Select Database

The third step is to select the database from the data tab and click on the Connect Button.

select database

Step 6: Data link property window

In this window we select SQL Server and click on the Next Button.

data link

Step 7: Connection tab


In the connection tab, enter the server name and user name and password and select the database from your server and click on the Ok button.

ok button

  • Then you find that the test connection succeeded.

    Confirm box

Step 8: Connected

Now you will see the connection to the SQL Server.

connection

Step 9: Provide path of data files and QVD files

Now the next step is to provide the path of the data files and QVD files using a set script variable.

path

Step 10: Select initial load tab

Now in this tab load the data file for the import data and select the table files option in the edit script window.

table files

Step 11: Open data file

The next step is to open our data file, like Excel, database or CSV file that you want to open.

local file

Step 12: File wizard type


Now this window will be opened and here you can see your data file.

file wizard

Step 13: Replace the path

Now replace the path of the data file with a script variable then reload it.

replace path

Step 14: Save QlikView file

After reloading the edit script, the next step is to save the QlikView file and click on the Save button.

save file

Step 15: Script execution progress

Then the script execution progress window will be opened and enter the required login Id and password and click on the OK button.

execution

Step 16: Sheet property window

After this process, the sheet property window will be opened and click on the Ok button.

main sheet

Step 17: Create QVD files

Open the edit script again and create QVD files and reload it again.

store function

Step 18: Script execution progress

Then the script execution progress window will be opened and enter the required login Id and password and click on the OK button.

execution

Step 19: Sheet property window

After this process, the sheet property window will be opened and click on the Ok button.

main sheet

Step 20: Select load QVD file tab

Open the edit script again and select load QVD file tab.

The next step is to load the QVD file that we create.

QVD file

Step 21: File wizard type

Now this window will be opened and here you can see you QVD file.

file wizard1

Now add this code in this tab to update the date in the QVD file.

code

Step 22: Next perform incremental load tab

Go to the next perform incremental load tab.

In this step load the incremental data from the source data and select the table files.

table files

Here you upload another data file (Excel) that is stored in the same location for the new QVD file.

Step 23: Open data file

The next step is to open our data file like Excel, database or CSV file that you want to open.

local file1

Step 24:
File wizard type

Now this window will be opened and here you can see your data file.

file wizard2

Now follow this code.

code1

Step 25: Select update QVD file tab

This tab concatenates incremental load data with the previous data by adding new records and replacing updated records.

Now the next step is to select the next tab and perform the code. In this step we again load the QVD file for concatenation purposes.

code2

After completing all the source code now reload it.

reload

Step 26: Script execution progress

Then the script execution progress window will be opened and enter the required login Id and password and click on the OK button.

execution

Step 27: Sheet property window

After this process, the sheet property window will be opened and click on the Ok button.

main sheet

Step 28: Open QVD file

After completing the reload process now open you QVD file. Now here you can see that the incremental data has been loaded.

final

Summary

This article described how incremental loads work and how to concatenate data from an Excel file to a QVD file.