topic Re: Incremental Reload using QVD in QlikView

Incremental Reload using QVD

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I really need help with the Incremental reload,

I have a Sales.QVD file which gets updated on a daily basis, so in my report I want to load only the new data from Last reload time but I also need to have the data before last reload time

for ex; in QVD I have 10k lines that has Last Reload Time = 15-06-2017, so if I made sales today and the QVD gone up to 10.5k lines, tomorrow when I reload I want the script to reload only the 0.5k lines from the QVD and concatenate with the existing 10k, instead of loading all 10.5m lines from the QVD,

Would you please help this is my script?

LET lastreloadtime = Date(reloadtime(),'DD-MM-YYYY');

Incremental_Sales:

LOAD date(date_sale) as date_of_sale,

product_code,

invoice_value

FROM

IncrementalReload.csv

[$(vFileLocation)Sales.qvd](qvd)

where date(date_sale,'DD-MM-YYYY') > $(lastreloadtime)

;

Concatenate

LOAD

date(date_sale) as date_of_sale,

product_code,

invoice_value

FROM

[$(vFileLocation)Sales.qvd](qvd)

where not exists (date_sale)

;

STORE Incremental_Sales into [$(vFileLocation)Sales.qvd](qvd)

I want to understand how to Store the 10k in Qlikview and just reload the 0.5k lines

Many thanks,

Re: Incremental Reload using QVD

kamal_sanguri — Fri, 16 Jun 2017 10:02:02 GMT

Hi Amir,

Go through this post https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

This will help you to understand/implement Incremental load in QV.

Re: Incremental Reload using QVD

Fri, 16 Jun 2017 11:16:46 GMT

Hello Kamal,

I have looked into this but this is talking about two different sources, but in my case it is just one qvd file, and I want to load based on Last reload time

Re: Incremental Reload using QVD

Kushal_Chawda — Fri, 16 Jun 2017 11:25:51 GMT

Why you need not exists condition? Your previous QVD will have old records you can simply append to the new records

LET lastreloadtime = Date(reloadtime(),'DD-MM-YYYY');

Incremental_Sales:

LOAD date(date_sale) as date_of_sale,

product_code,

invoice_value

FROM

IncrementalReload.csv

[$(vFileLocation)Sales.qvd](qvd)

where date(date_sale,'DD-MM-YYYY') > date#('$(lastreloadtime)','DD-MM-YYYY')

;

Concatenate

LOAD

date(date_sale) as date_of_sale,

product_code,

invoice_value

FROM

[$(vFileLocation)Sales.qvd](qvd)

;

STORE Incremental_Sales into [$(vFileLocation)Sales.qvd](qvd)

Re: Incremental Reload using QVD

Mon, 19 Jun 2017 08:53:48 GMT

Hello Kushal,

Thank you for your help.

But in this case my QVD will be reloading twice and then Storing the new data into the same QVD that the new and old data is coming from.

Incremental_Sales:

LOAD date(date_sale) as date_of_sale,

product_code,

invoice_value

FROM

[$(vFileLocation)Sales.qvd](qvd) (First reload)

where date(date_sale,'DD-MM-YYYY') > date#('$(lastreloadtime)','DD-MM-YYYY')

;

Concatenate

LOAD

date(date_sale) as date_of_sale,

product_code,

invoice_value

FROM

[$(vFileLocation)Sales.qvd](qvd) (Second reload)

STORE Incremental_Sales into [$(vFileLocation)Sales.qvd](qvd)

How does Incremental reload reduce Reload time?

Quote from https://community.qlik.com/message/29414?&_ga=2.20769918.790511996.1497860133-1484579794.1486400118#29414

Incremental reload is especially used to decrease the reload time, by for instance only loading new records from a data source instead of the complete table (which can be very big).

But in my case it is loading the complete QVD from the Concatenate script

Re: Incremental Reload using QVD

Kushal_Chawda — Mon, 19 Jun 2017 09:39:53 GMT

It will reduce the time when you load the data from the source, where you have millions of records. In this case incremental load will save the reload time. See the below scenario

Full load -> Takes 60 minutes to load the data from source (40 million records)

Now consider you applied incremental:

Incremental Data -> 1 minutes

Concatenate old QVD -> 10 minutes -> QVD load is faster

So total time is 11 minutes.