Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
dseelam
Creator II
Creator II

Incremental Load not working please help

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:

Capture.PNG

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

After Incremental load:

Capture2.PNG

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

1 Solution

Accepted Solutions
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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

Help users find answers! Don't forget to mark a solution that worked for you!

View solution in original post

8 Replies
Anonymous
Not applicable

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
Creator II
Creator II
Author

Hello Davendar,

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

Capture.PNG

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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

Help users find answers! Don't forget to mark a solution that worked for you!
dseelam
Creator II
Creator II
Author

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


Capture.PNG

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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 ?


Help users find answers! Don't forget to mark a solution that worked for you!
dseelam
Creator II
Creator II
Author

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

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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;


Help users find answers! Don't forget to mark a solution that worked for you!
dseelam
Creator II
Creator II
Author

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