Discussion Board for collaboration on QlikView Scripting.
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
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:
LOAD DATE(MAX(StyleCancelledDate),'MM/DD/YYYY') AS MaxDate
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);
"CUST_PO_NUMBER" as %CUST_PO,
"ITEM_STYLE" as ItemStyle,
"CANCELLED_QUANTITY" as CancelledQty,
"CANCEL_CODE" as CancelCode,
"PO_AGREEMENT_NUMBER" as [PO Agree],
date("CANCELLED_DATE" ) as StyleCancelledDate,
SQL SELECT *
FROM XXHHF."XXHSP_DB_CANCELLED_LINES_V" where "CANCELLED_DATE" > TO_DATE('$(Last_Updated_Date)','MM/DD/YYYY');
(qvd)where not Exists (%CUST_PO);
LOAD "CUST_PO_NUMBER" as %CUST_PO;
SQL SELECT "CUST_PO_NUMBER"
STORE Incremental into [$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd] (qvd);
drop Table Incremental;
Thanks In Advance
Go to Solution.
Use the not exists with two parameters.
"CUST_PO_NUMBER" as %CUST_PO_EXIST,
(qvd)where not Exists (%CUST_PO_EXIST, %CUST_PO);
after Connecting to the database add below one and reload
Once I done reloading, Its just gave me a single row with Max date in it
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
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
If you remove
Do you have the expected result ?
Yes But what if some thing got deleted in source this peace of code can take care of it ?
Right keep (Incremental)
LOAD distinct "CUST_PO_NUMBER" as %CUST_PO;
drop table _TEMP;
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