Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Please help an guide me where I correct myself
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:
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);
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
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
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
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
ID
FROM msdb.dbo.Customers
Please guide me.
How are you creating your QVD? Can you share your script?
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
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);
Thanks a lot sir for the help!!
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!!
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:
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);