Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to implement Incremental Load for delete & update data fectching from Oracle, below the script
I hope this is the correct one, but the load is taking long time sometimes it completes at 15mins ssometime it takes more than 2 hour.
Can anyone help on this as it's really urgent for me.
MaxDate:
LOAD
Year(Max(LAST_UPDATE_DATE)) - 2 as Date,
Year(Max(LAST_UPDATE_DATE)) - 1 as Year2
FROM "C:\Qlikview\[XYZ].QVD"
(qvd);
Let vSunMaxYear = Peek('Date');
Let vNotSunMaxYear = Peek('Year2');
Drop Table MaxDate;
If WeekDay(Today()) = 'Sunday' THEN
SundayData:
LOAD *
Where Year(LAST_UPDATE_DATE ) >= $(vSunMaxYear);
SQL SELECT *
FROM "ABC"."XYZ";
Concatenate
LOAD *
FROM
"C:\Qlikview\[XYZ].QVD"
(qvd)
WHERE Year(LAST_UPDATE_DATE )< $(vSunMaxYear);
STORE SundayData INTO $(vSales_Raw_Extractor)\[XYZ].QVD(qvd);
DROP Table SundayData;
ELSE
NotSundayData:
LOAD *
Where Year(LAST_UPDATE_DATE )>= $(vNotSunMaxYear);
SQL SELECT *
FROM "ABC"."XYZ";
Concatenate
LOAD * FROM
"C:\Qlikview\[XYZ].QVD"
(qvd)
WHERE Year(LAST_UPDATE_DATE )< $(vNotSunMaxYear);
STORE NotSundayData INTO $(vSales_Raw_Extractor)\[XYZ].QVD(qvd);
DROP Table NotSundayData;
Regards,
Renuka S
Hi Jonathan,
Thanks for your help, I got the answer for this. Below is the script.
MaxDate:
LOAD
Year(Max(LAST_UPDATE_DATE)) - 2 as Date,
Year(Max(LAST_UPDATE_DATE)) - 1 as Year2
FROM "C:\Qlikview\[XYZ].QVD"
(qvd);
Let vSunMaxYear = Peek('Date');
Let vNotSunMaxYear = Peek('Year2');
Drop Table MaxDate;
If WeekDay(Today()) = 'Sunday' THEN
SundayData:
SQL SELECT *
FROM "ABC"."XYZ"
where To_number(To_Char("LAST_UPDATE_DATE",'YYYY'),'9999')>=$(vSunMaxYear);
Concatenate
LOAD *
FROM
"C:\Qlikview\[XYZ].QVD"
(qvd)
WHERE Year(LAST_UPDATE_DATE )< $(vSunMaxYear);
STORE SundayData INTO $(vSales_Raw_Extractor)\[XYZ].QVD(qvd);
DROP Table SundayData;
ELSE
NotSundayData:
SQL SELECT *
FROM "ABC"."XYZ"
where To_number(To_Char("LAST_UPDATE_DATE",'YYYY'),'9999')>=$(vNotSunMaxYear);
Concatenate
LOAD * FROM
"C:\Qlikview\[XYZ].QVD"
(qvd)
WHERE Year(LAST_UPDATE_DATE )< $(vNotSunMaxYear);
STORE NotSundayData INTO $(vSales_Raw_Extractor)\[XYZ].QVD(qvd);
DROP Table NotSundayData;
Regards,
Renuka S
I don't think whether there is any performance issue for below script. Can you share whole script if anything hidden
Are you aware that this structure
SundayData:
LOAD *
Where Year(LAST_UPDATE_DATE ) >= $(vSunMaxYear);
SQL SELECT *
FROM "ABC"."XYZ";
fetches every record from the database and then filters the records in QV, You might want to move the Where to the SQL SELECT, and convert it to SQL syntax.
Hi Renuka,
I think you need to create a separate table for max date and pick max date from that table so it will improve some level.
Thanks,
Arvind Patil
Thanks for your prompt reply.
Yes I want to do Where clause in SQL Syntax, I tried using CAST(LAST_UPDATE_DATE as date) but its not working.
As I am fetching Data from Oracle. It would be helpful if you can help me on this.
Regards,
Renuka S
The Delete side of the incremental is not "as incremental" as other modes, because you need to fetch all the records anyway in order to determine what has been deleted -- unless the deletes can be assumed to be limited - for example: only current month records can be deleted. Or track deletes in an audit trail table in the database.
No hidden Script, I have just changed the Table Name.
Regards,
Renuka S
May be use something like below in SELECT statement
DATEPART(LAST_UPDATE_DATE, 'YY') >= Variable;
Hi,
Can you help me with the Script which I can replace with this as per the conditions.
IF(Sunday=3yrs,1yrs)
Regards,
Renuka S
Hi Jonathan,
Can you help me with script which will be useful to me where I can reduce my load time.
Regards,
Renuka S