Incremental load issue

Tue, 23 Oct 2012 05:09:46 GMT

Hi All,

I am new to qlikview, i am trying to implement incremental load

First i converted my SQL server table into Qvd... i have a field modication time and primary key.

I do want to implement incremental load for inser, update , delete

Can some one help me with error in my script

modification time is in format of 10/22/2012 3:22:45 AM (Please see attached image)

and my script is as follows

Max:

LOAD

Mod_Date

FROM

C:\Monica\Orders.qvd

(qvd);

Res:

Load Mod_Date as ModDate

Resident Max order by Mod_Date Desc;

Let MaxTimeStamp=Peek('ModDate',0);

Let MinDate=Peek('ModDate',-1);

Let MaxTIMESTAMP=Timestamp($(#MaxTimeStamp),'MM/DD/YYYY hh.mm.ss');

drop table Max,Res;

F_Orders:

SQL SELECT

"Order_ID",

"Create_By",

"Create_Date",

"Cust_Num",

"Bill_ID",

"Mod_By",

"Mod_Date"

FROM "Comp".dbo."F_Orders"

WHERE Mod_Date >= '$(MaxTIMESTAMP)';

Concatenate

LOAD

"Order_ID",

"Create_By",

"Create_Date",

"Cust_Num",

"Bill_ID",

"Mod_By",

"Mod_Date"

FROM

C:\Monica\Orders.qvd

(qvd)

where not Exists (Order_ID);

Inner Join

SQL SELECT "Order_ID"

FROM "Comp".dbo."F_Orders";

Store Orders into ;

Re: Incremental load issue

Tue, 23 Oct 2012 06:09:10 GMT

Hi,

use this:

1) Let MaxTIMESTAMP=Timestamp($(MaxTimeStamp),'YYYY-MM-DD hh.mm.ss');

instead of Let MaxTIMESTAMP=Timestamp($(#MaxTimeStamp),'MM/DD/YYYY hh.mm.ss');

2) also, where CONVERT(VARCHAR(50), Mod_Date, 120)>='$(MaxTIMESTAMP)';

SQL Server timestamps appear to be datetime but are actually binary strings. To convert it to actual datetime Convert has to be used.

For comparison,to have same format corresponding to the format modifier 120, YYYY-MM-DD hh.mm.ss' has been used in 1.

So,even if MaxTIMESTAMP is declared before the conevrt used in where clause, it has to be in accordance with available formats in SQL Server

Hope it helps !!!

Regards

topic Re: Incremental load issue in QlikView

Incremental load issue

Re: Incremental load issue