Saturday, March 5, 2016

Partial Reload in QlikView

Thanks For Stopping by and reading this post !!.

Partial Reload is the powerful feature in QlikView which help to add / replace data to QlikView Applications without reloading the complete application.

Usually When We do a full reload in QlikView application, QlikView drops all the tables from Memory and recreates the Table again from the Script But with Partial reload, we can reload only certain load/Select statement by Keeping other tables in QlikVeiw Memory. In other words When we use partial reload feature, “Load/Select/Map..Using” statements with Add/Replace only get Executed and all other remaining tables in QlikView Memory is retained.

There are two KeyWords by which partial reload can be activated in QlikView Scripting.

  1. Add       - Append Data to Existing Table or Add New Table to your Data Model
  2. Replace - Replace Existing Table.

Above Keywords can be prefixed only with below statement.

  1. Load
  2. Select
  3. Map...Using

Partial Reload can be triggered from File >> Partial Reload (Ctrl + Shift + R)

 

Partial Reload - Add Keyword :

ADD Keyword prefixed with “Load/Select/Map..Using” helps to append the data to existing table. And also it just does the blind append, it will not check for any Duplicate values.

For Example, i'm loading the simple month data with one field in my QlikView applications to demonstrate this Feature.

Loaded 3 months of data and done full refresh. My Data in data model looks as below.
Now i would like to add few more months without reloading my old data. For this i could use ‘ADD’ keyword with partial reload.

i have added  inline table with APR and MAY month with ADD keyword and done partial reload, So my month column has got a new records now.


To prove that QlikView will retain the tables without Add / Replace Keyword in Memory and Execute the only the Statement which has ADD/REPLCE Keyword, i have commented my 1st statement and Ran the Script again using Partial Reload. Below is the Output.


You could see the o/p above, it does added the New record to my existing table without dropping it. Also You could have spotted that Apr and May has been added twice as previous datamodel  had APR and MAY. This also proves that ADD Keyword would not check for any duplicates. it simply adds the records to Existing tables. If i rerun my script again, you would see APR and MAY Month added one more time and Frequency turns to 3.


This Can be Avoided using ‘Not Exist’  in Where clause or using Load ‘Distinct’ Table.

You would see the Effect of ADD keyword only with Partial Reload. With Full reload, QlikView Drops all the tables which are in Memory and loads it again so adding ADD Keyword is as good as doing  Straight Concatenation.

We could also force QlikView not to execute ADD Statement while doing full reload using ONLY Key word.

ADD ONLY Load …. From Table; (This would get executed only with partial reload)

Partial Reload - Replace Keyword :

REPLACE Keyword prefixed with “Load/Select/Map..Using” helps to replace the data if the table is already existing.  With the above Example, im planning to Replace JAN, FEB, MAR INLINE table with APR and MAY Inline Data. When I do a Partial Reload, QlikView Reads my Load statement which has REPLACE Keyword and see`s if the table with the same name exist and replaces it.

Step 1: QlikView Loads the Data from 1st Load Statement ( From Line No 13 to 19)
Step 2: QlikView Loads the Data from 2nd Load Statement ( From Line No 22 to 26) and Replaces the 1st load as the table Name is same.

This Replace statement Works with both Full Reload and Partial Reload.  This can be also forced to run only during the Partial Reload using ‘ONLY’ Keyword.

Partial Reload can be triggered from Command line using Batch file as below using ‘/rp’ or ‘/lp’

[ path ] Qv.exe [ { ‘/rp’ or ‘/lp’ } documentfile ]


Scenario
Add
Replace
What it does?
Appends the Data to the Existing Table
Drop Existing Table and Full Load
Concepts Works With?
Load / Select / Map..Using
Load / Select / Map..Using
Executed During Normal Reload?
Yes
Yes
Meaningful during Normal reloads?
No
Yes
Meaningful during partial reloads?
Yes
Yes
Check for duplicates is performed?
No, Appends the Data Without Checking for Duplicates
Drop Existing Table and Full Load (No Need of Duplicate Check)


QV Statements
Full Reload
Partial Reload
Load A, B From Table;
Drops Table and Full Load
Nothing
Add Load A, B From Table;
Uppend Data to Existing Table
Uppend Data to Existing Table
Add Only Load A, B From Table;
Nothing
Uppend Data to Existing Table
Replace Load A, B From Table;
Drop Table and Full Load
Drop Table and Full Load
Replace Only Load A, B From Table;
Nothing
Drop Table and Full Load

No comments:

Post a Comment