Incremental Load issue

[#1]

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;

[#2]

Hi,

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

[#3]

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

Regards,

Kiran Rokkam.

[#4]

Hi Vivek/Kiran,

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

[#5]

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.

[#6]

Hi Kiran,

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

Thanks

[#7]

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