Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
iam trying to implement the incremental load .
below is the script for that and the database is oracle.
after executing this is giving the error as shown in the screenshot .
Incremental:
LOAD
"Dept",
"RESOLVE_DATE",
"CLOSE_DATE",
LOAD_DATE,
"JIRA_PROJECT_KEY_ID";
From [..\dept.qvd(qvd)];
Last_Updated_Date:
LOAD
Date(max(LOAD_DATE,'MM-dd-YYYY')) as maxdate
Resident Incremental
LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');
concatenate
LOAD
"Dept",
"RESOLVE_DATE",
"CLOSE_DATE",
Date("LOAD_DATE") as LOAD_DATE,
"JIRA_PROJECT_KEY_ID";
SQL SELECT *
FROM "IPMO_SO".JIRA
where "LOAD_DATE" > '$(vLast_Updated_Date)';
Please need help on this
thanks in advance
Try this
Incremental:
LOAD
"Dept",
"RESOLVE_DATE",
"CLOSE_DATE",
LOAD_DATE,
"JIRA_PROJECT_KEY_ID";
From [..\dept.qvd(qvd)];
Last_Updated_Date:
LOAD
Date(max(LOAD_DATE),'MM/DD/YYYY') as maxdate
Resident Incremental
LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');
concatenate
LOAD
"Dept",
"RESOLVE_DATE",
"CLOSE_DATE",
Date("LOAD_DATE") as LOAD_DATE,
"JIRA_PROJECT_KEY_ID";
SQL SELECT *
FROM "IPMO_SO".JIRA
where "LOAD_DATE" > TO_DATE('$(vLast_Updated_Date)', 'mm/dd/yyyy');
Try this
Last_Updated_Date:
LOAD
Date(max(LOAD_DATE,'DD-MMM-YYYY')) as maxdate
Resident Incremental
LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');
Hi - Try this
your maxdate is in 'MM-dd-YYYY format but LOAD_DATE is in MM/ddYYYY.
change the format accordingly and make sure LOAD_DATE & vLast_Updated_Date are in the same format
updated script -->
Last_Updated_Date:
LOAD
Date(max(LOAD_DATE,'MM/dd/YYYY')) as maxdate
Resident Incremental
LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');
Actually this
Last_Updated_Date:
LOAD
Date(max(LOAD_DATE),'DD-MMM-YYYY') as maxdate
Resident Incremental
LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');
Hi Sunny ,
Thanks for the reply ..
With the above logic it is loading double records ... and my load date format within the QVD file MM-dd-yyyy(4/24/2018)
when i run the script with your logic it is loading double records ( daily we do load 30000 records) now it is loading 60000 record
do i need to make any change ?
When you use this, the record count doubles, really??
Incremental:
LOAD
"Dept",
"RESOLVE_DATE",
"CLOSE_DATE",
LOAD_DATE,
"JIRA_PROJECT_KEY_ID";
From [..\dept.qvd(qvd)];
Last_Updated_Date:
LOAD
Date(max(LOAD_DATE),'DD-MMM-YYYY') as maxdate
Resident Incremental
LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');
concatenate
LOAD
"Dept",
"RESOLVE_DATE",
"CLOSE_DATE",
Date("LOAD_DATE") as LOAD_DATE,
"JIRA_PROJECT_KEY_ID";
SQL SELECT *
FROM "IPMO_SO".JIRA
where "LOAD_DATE" > '$(vLast_Updated_Date)';
I mean in the reloading script first it fetches the qvd which is 30000 then it shows
last updtaed date 1 line fetched
and third line it should show 10000 records but actaully it is showing 40000 records which is wrong ..
i hope u understand what i am saying ...seeing this i have to kill the process
How does your variable look like? 24-Apr-2018 or something else?
Hi Sunny ,
No sunny i just took the screen shot from the debug while still the process was runing
and the values within vLast_Updated_Date is like this
But it is keeping on loading the records ...
live data is like till 24 aprill my data is around 50 lakhs rows and daily it will be around 20 thousand rows insertion ..
but this is keeping on loading ...
there is something wrong
Try this
Incremental:
LOAD
"Dept",
"RESOLVE_DATE",
"CLOSE_DATE",
LOAD_DATE,
"JIRA_PROJECT_KEY_ID";
From [..\dept.qvd(qvd)];
Last_Updated_Date:
LOAD
Date(max(LOAD_DATE),'MM/DD/YYYY') as maxdate
Resident Incremental
LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');
concatenate
LOAD
"Dept",
"RESOLVE_DATE",
"CLOSE_DATE",
Date("LOAD_DATE") as LOAD_DATE,
"JIRA_PROJECT_KEY_ID";
SQL SELECT *
FROM "IPMO_SO".JIRA
where "LOAD_DATE" > TO_DATE('$(vLast_Updated_Date)', 'mm/dd/yyyy');