Issue of Incremental Load

[#1]

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

[#2]

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);

[Anonymous]

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

[rwunderlich]

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

[#5]

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.

[Anonymous]

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