topic Re: Data load in QlikView

Data load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

Please help me in the incremental data load.

I have declared the variable which pulls the max. date, then used this variable in main script.

The issue I am facing is that when I first upload the data it loads the new data correctly

When I reload the data it shows that no data is loaded i.e. 0 lines fetched but it removes the old data.

When I reload the data again it pulls all the data from the starting

I want that it pulls the new data only. When I reload the data it pulls zero lines with old data in it and this process repeats again and again

LOAD SCRIPT:

LIB CONNECT TO 'TIGUNVMBGWL0011';

LOAD CustomerName,

ContactName,

POSTDATE;

SQL SELECT CustomerName,

ContactName,

POSTDATE

FROM msdb.dbo.Customers

Where POSTDATE >'$(Last_Update_Date)';

VARIABLE:

Last_Update:

LOAD Max(POSTDATE) as maxdate

resident Customers;

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'YYYY-MM-DD');

Please find the snapshot of the load

When I load the data first time it loads the new data only
When I reload the data it show 0 lines fetched
When I reload the data it loads all the data

Please help an guide me where I correct myself

reddys310‌

New to Qlik Sense‌

Re: Data load

Anonymous — Sat, 10 Sep 2016 14:31:44 GMT

Can you post the whole script?

I believe you are not saving the data into a QVD file, you cannot skip this when using incremental loads.

Regards

Re: Data load

Anonymous — Sat, 10 Sep 2016 14:55:10 GMT

Thanks for your reply sir!!

The above mentioned is my whole script.. Do I miss something please let me know..

This is just a dummy data. I need to apply this in my whole data set in which everyday lakhs of rows are added.

Yep. I am not saving the data in QVD file.

If I understand correctly from your second line I have no option to skip this. But this will create a problem for me if the data loads twice because it removes all the data second time

Please suggest

Thanks

Re: Data load

Anonymous — Sat, 10 Sep 2016 15:00:26 GMT

Hi,

Please see this webpage. You are missing a couple things to make an incremental load work. You need to read a QVD with previous data and append new data to it, it cannot be done on the fly from a database.

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Regards,

Oscar

Re: Data load

Anonymous — Sun, 11 Sep 2016 05:33:33 GMT

Thanks a lot sir. It really helps!!

But i have few doubts. It would be great if you resolve my doubts.

i have created a qvd which pulls all data in it. I have declared a variable by which I am able to know my last date.

I use this qvd for incremental load by following the above link steps ( Insert, Update & Delete).

Queries:

1. Do I need to declare a variable in my incremental load QVD, if yes how and if not how it reads the variable which pulls the latest date

2. In the link, we have inner join with the ID, what will happen if we do not have any unique id or if we have multiple id

3. I am missing something because still using the variable it is loading full data set, not those with the latest date data. Might be the variable is not working fine as I have not declared the variable in Incremental load

QVD WHICH PULLS ALL DATA AND LAST DATE:

LIB CONNECT TO 'TIGUNVMBGWL0011';

SALES:

LOAD CustomerName,

ContactName,

POSTDATE;

SQL SELECT CustomerName,

ContactName,

POSTDATE

FROM msdb.dbo.Customers;

Last_Update:

LOAD Max(POSTDATE) as maxdate

resident SALES;

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'YYYY-MM-DD');

INCREMENTAL LOAD QVD:

LIB CONNECT TO 'TIGUNVMBGWL0011';

Incrementals:

LOAD

ID,

CustomerName,

ContactName,

POSTDATE;

SQL SELECT

ID,

CustomerName,

ContactName,

POSTDATE

FROM msdb.dbo.Customers

Where POSTDATE >'$(Last_Update_Date)';

CONCATENATE

LOAD

FROM 'LIB://Qlik_QVD/SALES.qvd'(qvd);

INNER JOIN

LOAD

ID;

SQL SELECT

FROM msdb.dbo.Customers

Please guide me.

Re: Data load

Anonymous — Sun, 11 Sep 2016 16:25:38 GMT

How are you creating your QVD? Can you share your script?

Re: Data load

Anonymous — Sun, 11 Sep 2016 17:03:37 GMT

Hello Sir

QVD Creation Script:

LIB CONNECT TO 'TIGUNVMBGWL0011';

SALES:

LOAD CustomerName,

ContactName,

POSTDATE;

SQL SELECT CustomerName,

ContactName,

POSTDATE

FROM msdb.dbo.Customers;

Last_Update:

LOAD Max(POSTDATE) as maxdate

resident SALES;

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'YYYY-MM-DD');

STORE SALES

INTO 'LIB://Qlik_QVD/SALES.QVD';

INCREMENTAL LOAD SCRIPT:

LIB CONNECT TO 'TIGUNVMBGWL0011';

Incrementals:

LOAD

ID,

CustomerName,

ContactName,

POSTDATE;

SQL SELECT

ID,

CustomerName,

ContactName,

POSTDATE

FROM msdb.dbo.Customers

Where POSTDATE >'$(Last_Update_Date)';

CONCATENATE

LOAD

FROM 'LIB://Qlik_QVD/SALES.qvd'(qvd);

STORE Incrementals

INTO 'LIB://Qlik_QVD/Incrementals.QVD';

Attached is the qvd files

Please let me know where is the issue

Thanks

Re: Data load

Anonymous — Sun, 11 Sep 2016 17:19:46 GMT

The incremental load should happen in the same script (you can split it in two different tabs, but not different scripts). Here's what I believe you need.... let me know if this works. Regards

LIB CONNECT TO 'TIGUNVMBGWL0011';

SET QVD_Path = 'LIB://Qlik_QVD/SALES.QVD';

LET vQVDExists = NOT isnull(QVDCreateTime('$(QVD_Path)'));

IF $(vQVDExists) THEN

SALES:

LOAD *

FROM

[$(QVD_Path)]

(qvd);

Last_Update:

LOAD Max(POSTDATE) as maxdate

resident SALES;

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'YYYY-MM-DD');

Set vConcatenate = Concatenate;

ENDIF

$(vConcatenate)

LOAD ID,

CustomerName,

ContactName,

POSTDATE;

SQL SELECT ID,

CustomerName,

ContactName,

POSTDATE

FROM msdb.dbo.Customers

Where POSTDATE >'$(Last_Update_Date)';

STORE SALES INTO $(QVD_Path) (QVD);

Re: Data load

Anonymous — Mon, 12 Sep 2016 03:04:56 GMT

Thanks a lot sir for the help!!

Yes, this is same thing I want.

But in loading we have the Synthetic Key, how we remove that key. There is a table "Last_Update" I am not able to find out where this table is created.

It also shows an error while doing CONCATENATE

Below is the screenshot of it:

It would be grateful for me if you can explain me what you did in the script part briefly and also help me to remove the synthetic key.

Thanks a lot for help!!

Please guide me

Thanks!!

Re: Data load

Anonymous — Mon, 12 Sep 2016 14:09:56 GMT

Hi,

The "error" in the concatenate is ok, QlikView has some bugs like this, there's no error but still you'll see it. To remove the sintetic key add this right after the ENDIF

DROP TABLE Last_Update;

So basically I'm doing this:

Check if there's a QVD already
1. If yes, then
  1. Load the current content from it
  2. Append new data from database
  3. Store all the dataset in the QVD (this new version overwrites the old one)
2. If no, then
  1. Read the data directly from database
  2. Store the data in new QVD to be used next time you run it

By the way I forgot to add a variable value in case the QVD does not exist... so the whole script would be this

LIB CONNECT TO 'TIGUNVMBGWL0011';

SET QVD_Path = 'LIB://Qlik_QVD/SALES.QVD';

LET vQVDExists = NOT isnull(QVDCreateTime('$(QVD_Path)'));

Let Last_Update_Date = '2000/01/01'; //Use whatever date you want here to pull all your data from database

IF $(vQVDExists) THEN

SALES:

LOAD *

FROM

[$(QVD_Path)]

(qvd);

Last_Update:

LOAD Max(POSTDATE) as maxdate

resident SALES;

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'YYYY-MM-DD');

Set vConcatenate = Concatenate;

ENDIF

DROP TABLE Last_Update; //getting rid of temporal table created to pull max date from QVD to avoid syntetic keys

$(vConcatenate)

LOAD ID,

CustomerName,

ContactName,

POSTDATE;

SQL SELECT ID,

CustomerName,

ContactName,

POSTDATE

FROM msdb.dbo.Customers

Where POSTDATE >'$(Last_Update_Date)';

STORE SALES INTO $(QVD_Path) (QVD);

Re: Data load

Anonymous — Sat, 17 Sep 2016 06:06:24 GMT

Thanks a lot sir!!

Just a small query, if the QVD does not exist and I want that pass the variable which dynamically pull the data from the first day.. if yes then how??

Thanks again!!

Re: Data load

Anonymous — Mon, 19 Sep 2016 13:46:20 GMT

take this line of the script out into an include file so you can modify it externally

Let Last_Update_Date = '2000/01/01'; //Use whatever date you want here to pull all your data from database

https://www.quickintelligence.co.uk/qlikview-include-files/