topic Re: Incremental Load not working please help in QlikView

Incremental Load not working please help

dseelam — Thu, 03 Nov 2016 13:40:59 GMT

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

Re: Incremental Load not working please help

Anonymous — Thu, 03 Nov 2016 14:12:07 GMT

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

Re: Incremental Load not working please help

dseelam — Thu, 03 Nov 2016 14:28:24 GMT

Hello Davendar,

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

Re: Incremental Load not working please help

Aurelien_Martinez — Thu, 03 Nov 2016 14:39:31 GMT

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

Re: Incremental Load not working please help

dseelam — Thu, 03 Nov 2016 15:08:33 GMT

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

Re: Incremental Load not working please help

Aurelien_Martinez — Thu, 03 Nov 2016 15:22:59 GMT

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 ?

Re: Incremental Load not working please help

dseelam — Thu, 03 Nov 2016 15:24:38 GMT

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

Re: Incremental Load not working please help

Aurelien_Martinez — Thu, 03 Nov 2016 15:29:20 GMT

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;

Re: Incremental Load not working please help

dseelam — Thu, 03 Nov 2016 15:36:24 GMT

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