topic Re: Issue of Incremental Load in QlikView

Issue of Incremental Load

Wed, 15 Feb 2012 07:22:51 GMT

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

Re: Issue of Incremental Load

Wed, 15 Feb 2012 08:07:21 GMT

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

Issue of Incremental Load

Anonymous — Fri, 17 Feb 2012 12:39:05 GMT

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

Issue of Incremental Load

rwunderlich — Mon, 20 Feb 2012 05:24:01 GMT

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

Re: Issue of Incremental Load

Mon, 20 Feb 2012 13:14:52 GMT

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.

Re: Issue of Incremental Load

Anonymous — Mon, 20 Feb 2012 13:25:27 GMT

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