Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
dseelam
Contributor

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
amartinez35
Valued Contributor

Re: Incremental Load not working please help

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

8 Replies
lakkydev
Contributor II

Re: Incremental Load not working please help

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
Contributor

Re: Incremental Load not working please help

Hello Davendar,

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

Capture.PNG

amartinez35
Valued Contributor

Re: Incremental Load not working please help

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
Contributor

Re: Incremental Load not working please help

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

amartinez35
Valued Contributor

Re: Incremental Load not working please help

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
Contributor

Re: Incremental Load not working please help

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

amartinez35
Valued Contributor

Re: Incremental Load not working please help

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
Contributor

Re: Incremental Load not working please help

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

Community Browser