Solved: Incremental Load : Delete & Update - Qlik Community

renuka_sasikumar · ‎2017-07-25

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

renuka_sasikumar · ‎2017-07-27

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

View solution in original post

Anil_Babu_Samineni · ‎2017-07-25

I don't think whether there is any performance issue for below script. Can you share whole script if anything hidden

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

jonathandienst · ‎2017-07-25

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

arvind_patil · ‎2017-07-25

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

renuka_sasikumar · ‎2017-07-25

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

jonathandienst · ‎2017-07-25

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

renuka_sasikumar · ‎2017-07-25

No hidden Script, I have just changed the Table Name.

Regards,

Renuka S

Anil_Babu_Samineni · ‎2017-07-25

May be use something like below in SELECT statement

DATEPART(LAST_UPDATE_DATE, 'YY') >= Variable;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

renuka_sasikumar · ‎2017-07-25

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

renuka_sasikumar · ‎2017-07-25

Hi Jonathan,

Can you help me with script which will be useful to me where I can reduce my load time.

Regards,

Renuka S