Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Brothers
I am using ms sql server as my data source and looking forward to perform incremental reload on it
let LastExecTime=?;
let ModificationTime=?;
QV_Table:
LOAD customer,
customername,
city,
state;
SQL SELECT *
FROM demo.dbo.customers
Where $(ModificationTime) >= $(LastExecTime);
what should be defination of above varables.
Please let me have an example file on this topic
Hope it will help to understand the incremental reload concept
Refer the files attached.
OLEDB CONNECT32 TO [Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog="";Data Source=IRFANGHORI-PC;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=IRFANGHORI-PC;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False];
//Orders:
//LOAD orderID,
// customer,
// orderDate,
// ShippingCost;
//SQL SELECT *
//FROM demo.dbo.orders;
//
//STORE Orders into Orders_qvd.qvd(qvd);
//DROP Table Orders;
set DeletedFlag=0;
let LastExecTime=today();
let ModificationTime=today()-1;
let BeginningThisExecTime=today()+1;
ListOfDeletedEntries:
LOAD orderID as Deleted;
// customer,
// orderDate,
// ShippingCost;
SQL SELECT orderID
FROM demo.dbo.orders
Where $(DeletedFlag) = 1;
Orders:
LOAD orderID,
customer,
orderDate,
ShippingCost;
SQL SELECT *
FROM demo.dbo.orders
Where $(ModificationTime) >=$(LastExecTime)
and $(ModificationTime) <= $(BeginningThisExecTime);
Concatenate
LOAD orderID,
customer,
orderDate,
ShippingCost
FROM
Orders_qvd.qvd
(qvd)
where not Exists(orderID)
and
not Exists(deleted,orderID);
STORE Orders into Orders_qvd.qvd(qvd);
Hi
ModificationTime --> is refering the database last modified time (when the user insert/update/deledte any data its created one log file using the stored procedure or triggers)
LastExecTime --> its referring the last reloaded time of your qlikview dashboard. here you can set the last reloaded time as like below
Let LastExecTime = Date(ReloadTime(),'YYYY-MM-DD'); (here i mention the date format which is match with sql server date format)
Then you script like below,
Let LastExecTime = Date(ReloadTime(),'YYYY-MM-DD');
QV_Table:
LOAD customer,
customername,
city,
state;
SQL SELECT *
FROM demo.dbo.customers
Where $(databaseTableDateField) >= $(LastExecTime);
Regards
Ashok
You can use qlikview components http://qlikviewcomponents.org to simplify incremental load. See also
http://qlikviewnotes.blogspot.com/2012/01/incremental-load-using-qlikview.html
Rob
Hi chandashok,
Hope you are fine, i am looking farword for your assistance,
The following query provide me the last updated date and time of the specified table
SELECT top 1
last_user_update
FROM sys.dm_db_index_usage_stats
where OBJECT_NAME(OBJECT_ID)= 'orders';
But the question i have is how to use this date as ModificationTime in my file.
Hi
No need to take the last update date from the database.
From qlikview itself you can compare the date and take the data from the database
Let LastExecTime = Date(ReloadTime(),'YYYY-MM-DD');
SELECT *
FROM sys.dm_db_index_usage_stats
where OBJECT_NAME(OBJECT_ID)= 'orders'
And
your_database_table_dateField >= LastExecTime ;
Try this
Regards
Ashok