topic Re: Incremental Load issue in QlikView

Incremental Load issue

Wed, 11 Jul 2012 01:21:41 GMT

Hi all,

the modification date(time stamp) i get from database is in format

7/10/2012 1:43:22 AM

i am trying to achieve incremental load .... i belive my script is doing full reload ..

Appreciate any help how to achive incremental load

Max:

LOAD

Mod_Date

FROM

C:\Document\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),'YYYY-MM-DD-hh.mm.ss');

drop table Max,Res;

Orders:

LOAD

"Order_ID",

"Create_By",

"Create_Date",

"Mod_By",

"Mod_Date",

"Order_Num";

SQL SELECT *

FROM "Orders_dw".dbo."Orders"

WHERE "Mod_Date" >= '$(MaxTIMESTAMP)';

CONCATENATE

LOAD

Order_ID,

Create_By,

Create_Date,

Mod_By,

Mod_Date

Order_Num;

FROM

C:\Document\Orders.qvd

(qvd) WHERE NOT EXISTS(Order_ID);

Store Orders into C:\Document\Orders.qvd

Drop Table Orders;

Re: Incremental Load issue

Wed, 11 Jul 2012 03:03:13 GMT

Hi,

Covert the date in num format when passing it to the variable. And also convert theMod_Date to num.

Re: Incremental Load issue

Wed, 11 Jul 2012 03:05:39 GMT

In the reload log, is the where condition as expected?

Regards,

Kiran Rokkam.

Re: Incremental Load issue

Wed, 11 Jul 2012 11:20:41 GMT

Hi Vivek/Kiran,

Thanks for your response, Is there any thing i have to fix in my script

Re: Incremental Load issue

Wed, 11 Jul 2012 11:39:16 GMT

Monica:

Few things have to validated in the script is if the SQL script is understanding the variable passed in the condition. In incremental load, time condition if very critical in comparision with full reload. Since you believe this is a full reload the timestamp format is key.

To confirm your belief, check the log files to see the SQL query executed for the incremental part.

Personally I use file functions like QVD Creation time for incremental load.

Regards,

Kiran Rokkam.

Re: Incremental Load issue

Wed, 11 Jul 2012 11:44:20 GMT

Hi Kiran,

Can you post the script you are using so that i can try if it works for me

Thanks

Re: Incremental Load issue

Wed, 11 Jul 2012 11:56:59 GMT

Let lastReloadDate = Date(QvdCreateTime([..\QVD\Inventory.qvd]),'DD-MMM-YYYY');

TempInvTransData:

SQL SELECT ORGANIZATION_ID, SUBINVENTORY_CODE,INVENTORY_ITEM_ID,TRANSACTION_DATE

SUM(CASE WHEN PRIMARY_QUANTITY <= 0 THEN PRIMARY_QUANTITY ELSE 0 END) AS ISSUEDQTY,

SUM(CASE WHEN PRIMARY_QUANTITY >= 0 THEN PRIMARY_QUANTITY ELSE 0 END) AS RECIEVEDQTY

FROM MTL_MATERIAL_TRANSACTIONS

WHERE TRANSACTION_DATE >= '$(lastReloadDate)' AND SUBINVENTORY_CODE IS NOT NULL

GROUP BY ORGANIZATION_ID,SUBINVENTORY_CODE,INVENTORY_ITEM_ID;

LOAD PUItemID,InventoryTypeID,CalendarDate, InventoryQty, IssuedQty, RecievedQty FROM [..\QVD\Inventory.qvd](qvd);