topic Re: Incremental Load : Delete & Update in QlikView

Incremental Load : Delete & Update

renuka_sasikumar — Tue, 25 Jul 2017 06:02:02 GMT

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

Re: Incremental Load : Delete & Update

Anil_Babu_Samineni — Tue, 25 Jul 2017 06:40:31 GMT

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

Re: Incremental Load : Delete & Update

jonathandienst — Tue, 25 Jul 2017 06:46:19 GMT

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.

Re: Incremental Load : Delete & Update

arvind_patil — Tue, 25 Jul 2017 06:50:27 GMT

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

Re: Incremental Load : Delete & Update

renuka_sasikumar — Tue, 25 Jul 2017 06:50:40 GMT

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

Re: Incremental Load : Delete & Update

jonathandienst — Tue, 25 Jul 2017 06:51:12 GMT

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.

Re: Incremental Load : Delete & Update

renuka_sasikumar — Tue, 25 Jul 2017 06:51:14 GMT

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

Regards,

Renuka S

Re: Incremental Load : Delete & Update

Anil_Babu_Samineni — Tue, 25 Jul 2017 06:55:11 GMT

May be use something like below in SELECT statement

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

Re: Incremental Load : Delete & Update

renuka_sasikumar — Tue, 25 Jul 2017 07:15:26 GMT

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

Re: Incremental Load : Delete & Update

renuka_sasikumar — Tue, 25 Jul 2017 07:50:38 GMT

Hi Jonathan,

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

Regards,

Renuka S

Re: Incremental Load : Delete & Update

jonathandienst — Wed, 26 Jul 2017 13:23:58 GMT

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..

Re: Incremental Load : Delete & Update

renuka_sasikumar — Thu, 27 Jul 2017 08:17:25 GMT

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