Incremental Load : Delete & Update

[renuka_sasikumar]

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

[jonathandienst]

I cant help with Oracle code - I have not had much need for it in the places I work. Perhaps someone else can help here, or go to the Oracle community site for more help..

[renuka_sasikumar · Accepted solution]

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