Incremental Load not working please help

[dseelam]

Hello All,

Some how my first incremental load try is not working properly as expected, I don't know if I am missing some thing!

In the 1st reload from DB am able to load 183755 rows after incremental some how its just need to insert update & delete records it is loading only 37470

Before Incremental:

I can see only 1,83,755 rows after dumping everything in to excel

After Incremental load:

I can see only 37,470 rows after dumping everything in to excel

Script for incremental:

Cancelled:

LOAD ORGANIZATION_CODE,

    %CUST_PO,

    ORDER_NUMBER,

    ORDER_LINE_NUMBER,

    ItemStyle,

    CancelledQty,

    CancelCode,

    [PO Agree],

    StyleCancelledDate,

    ORDER_CREATION_DATE

FROM

[$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd]

(qvd);

Last_Updated_Date:

LOAD DATE(MAX(StyleCancelledDate),'MM/DD/YYYY') AS MaxDate

Resident Cancelled;

let Last_Updated_Date  = DATE(peek('MaxDate',0,'Last_Updated_Date'),'MM/DD/YYYY');

drop Table Cancelled;

OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=SQLREAD;Data Source=TST;Extended Properties=""] (XPassword is PNBLVbVMMbYeWWVMRRMGTFA);

Incremental:

LOAD "ORGANIZATION_CODE",

    "CUST_PO_NUMBER" as %CUST_PO,

    "ORDER_NUMBER",

    "ORDER_LINE_NUMBER",

    "ITEM_STYLE" as ItemStyle,

    "CANCELLED_QUANTITY" as CancelledQty,

    "CANCEL_CODE" as CancelCode,

    "PO_AGREEMENT_NUMBER" as [PO Agree],

    date("CANCELLED_DATE" ) as  StyleCancelledDate,

    "ORDER_CREATION_DATE";

SQL SELECT *

FROM XXHHF."XXHSP_DB_CANCELLED_LINES_V" where "CANCELLED_DATE" > TO_DATE('$(Last_Updated_Date)','MM/DD/YYYY');

Concatenate

LOAD ORGANIZATION_CODE,

    %CUST_PO,

    ORDER_NUMBER,

    ORDER_LINE_NUMBER,

    ItemStyle,

    CancelledQty,

    CancelCode,

    [PO Agree],

    StyleCancelledDate,

    ORDER_CREATION_DATE

FROM

[$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd]

(qvd)where not Exists (%CUST_PO);

Inner join

LOAD "CUST_PO_NUMBER" as %CUST_PO;

SQL SELECT "CUST_PO_NUMBER"

FROM XXHHF."XXHSP_DB_CANCELLED_LINES_V";

STORE Incremental into [$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd] (qvd);

drop Table Incremental;

Cancelled_Lines:

LOAD ORGANIZATION_CODE,

    %CUST_PO,

    ORDER_NUMBER,

    ORDER_LINE_NUMBER,

    ItemStyle,

    CancelledQty,

    CancelCode,

    [PO Agree],

    StyleCancelledDate,

    ORDER_CREATION_DATE

FROM

[$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd]

(qvd);

Thanks In Advance

[Anonymous]

after Connecting to the database add below one and reload

Incremental:

LOAD "ORGANIZATION_CODE",

    "CUST_PO_NUMBER" as %CUST_PO,

    "ORDER_NUMBER",

    "ORDER_LINE_NUMBER",

    "ITEM_STYLE" as ItemStyle,

    "CANCELLED_QUANTITY" as CancelledQty,

    "CANCEL_CODE" as CancelCode,

    "PO_AGREEMENT_NUMBER" as [PO Agree],

    date("CANCELLED_DATE" ) as  StyleCancelledDate,

    "ORDER_CREATION_DATE";

SQL SELECT *

FROM XXHHF."XXHSP_DB_CANCELLED_LINES_V" where "CANCELLED_DATE" > TO_DATE('$(Last_Updated_Date)','MM/DD/YYYY');

STORE Incremental into [$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd] (qvd);

Concatenate

LOAD ORGANIZATION_CODE,

    %CUST_PO,

    ORDER_NUMBER,

    ORDER_LINE_NUMBER,

    ItemStyle,

    CancelledQty,

    CancelCode,

    [PO Agree],

    StyleCancelledDate,

    ORDER_CREATION_DATE

FROM

[$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd]

(qvd)where not Exists (%CUST_PO);

[dseelam]

Hello Davendar,

Once I done reloading, Its just gave me a single row with Max date in it

[Aurelien_Martinez · Accepted solution]

Hi,

Use the not exists with two parameters.

Incremental:

LOAD "ORGANIZATION_CODE",

    "CUST_PO_NUMBER" as %CUST_PO,

  "CUST_PO_NUMBER" as %CUST_PO_EXIST,

    "ORDER_NUMBER",

    "ORDER_LINE_NUMBER",

    "ITEM_STYLE" as ItemStyle,

    "CANCELLED_QUANTITY" as CancelledQty,

    "CANCEL_CODE" as CancelCode,

    "PO_AGREEMENT_NUMBER" as [PO Agree],

    date("CANCELLED_DATE" ) as  StyleCancelledDate,

    "ORDER_CREATION_DATE";

SQL SELECT *

FROM XXHHF."XXHSP_DB_CANCELLED_LINES_V" where "CANCELLED_DATE" > TO_DATE('$(Last_Updated_Date)','MM/DD/YYYY');

Concatenate

LOAD ORGANIZATION_CODE,

    %CUST_PO,

    ORDER_NUMBER,

    ORDER_LINE_NUMBER,

    ItemStyle,

    CancelledQty,

    CancelCode,

    [PO Agree],

    StyleCancelledDate,

    ORDER_CREATION_DATE

FROM

[$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd]

(qvd)where not Exists (%CUST_PO_EXIST, %CUST_PO);

[dseelam]

Aurelien,  thank you

If I am doing what you suggested I am able to see all the 183755 rows when exported to excel, which are inserted and updated records in Source

what about inner join after the concatenating Not existqvd.

which is in my code

Inner join

LOAD "CUST_PO_NUMBER" as %CUST_PO;

SQL SELECT "CUST_PO_NUMBER"

FROM XXHHF."XXHSP_DB_CANCELLED_LINES_V";

don't we need it to delete records in QVD which are deleted in Source ?

for an instance if I use it I am able to see millions of records, which doesn't make any sense

[Aurelien_Martinez]

If you remove

Inner join

LOAD "CUST_PO_NUMBER" as %CUST_PO;

SQL SELECT "CUST_PO_NUMBER"

FROM XXHHF."XXHSP_DB_CANCELLED_LINES_V";

Do you have the expected result ?

[dseelam]

Yes But what if some thing got deleted in source this peace of code can take care of it ?

[Aurelien_Martinez]

Try,

_TEMP:

Right keep (Incremental)

LOAD distinct  "CUST_PO_NUMBER" as %CUST_PO;

SQL SELECT "CUST_PO_NUMBER"

FROM XXHHF."XXHSP_DB_CANCELLED_LINES_V";

drop table _TEMP;

[dseelam]

Aurelien,

It dint do kind of change num of records I have expected results, but I just want to make sure it will delete the deleted record in source