topic Re: Incremental load in Qlikview in QlikView

Incremental load in Qlikview

Tue, 11 Jun 2013 08:11:28 GMT

Hello Friends,

I have one oracle table. in that table there are few columns for example.

ID (Number)

Name (Text)

Description (Text)

RowVersion (Number)

Now i want to implement increment load in above table. It means i want to get new records and modified records. Here there is not date column.

I know one way using modified date column. But here once any record change RowVersion column will be increment by one count.

Can any one help me to solved this problem?

Thank

Re: Incremental load in Qlikview

sujeetsingh — Tue, 11 Jun 2013 08:28:14 GMT

Hi,

Go to this link for concept of Incremental Load

http://www.quickintelligence.co.uk/qlikview-incremental-load/

Re: Incremental load in Qlikview

Tue, 11 Jun 2013 09:02:45 GMT

try this

Master:

ID,

Name,

Description,

RowVersion

from Master.qvd;

Max:

load max(ID) as maxID

resident Master;

Let vMax = peek('maxID',0,'Max')

Drop table Max;

Transaction:

join(Master)

Load

ID,

Name,

Description,

RowVersion

From Transaction.qvd

where ID >'$(vMax)';

see attachment

Re: Incremental load in Qlikview

Tue, 11 Jun 2013 09:40:56 GMT

I think this will not work because RowVersion will update if that records is update.

I want to get new and updated records from database and old from QVD file.

Re: Incremental load in Qlikview

amars — Tue, 11 Jun 2013 09:52:53 GMT

Hi Dipak,

You can try it this way, create a variable in your application which will hold the Max(RowVersion). Now for incremental Load use this variable and Load data where RowVersion > $(varMaxRowVersion).

Now use Exist and Load data where ID is not existing in allready loaded data.

Let varMaxRowVersion = New Max varMaxVersion for next reload.

Thanks...

Amar

Re: Incremental load in Qlikview

Tue, 11 Jun 2013 10:02:36 GMT

Hi Amar,

Thank you for reply.

I think this will not work. let me explain this.

EX.

LOAD NO. 1:

------------------------------

ID = 1

Name = NAME1

Description = DESC 1

RowVersion = 1

ID = 2

Name = NAME2

Description = DESC 2

RowVersion = 1

ID = 3

Name = NAME3

Description = DESC 3

RowVersion = 1

--------------------------------------------

Now record id = 2 is updated one time, it means RowVersion of that record are = 2.

and record id = 3 is updated two time, it means RowVersion of that record are = 3.

Now in next reload Record id = 2 and 3 will get from database becuase they are updated. and record id = 1 will fetch from QVD file.

Please let me know if you have any question.

Thanks

Re: Incremental load in Qlikview

amars — Tue, 11 Jun 2013 10:15:05 GMT

Hi Dipak,

In that case, I don't think there is any way to identify newly added, updated records (as even the RowVersion Number is specifc to the ID & is not for the whole data records as such).

This is not Incremental Load but If you wish you can try this...

Table:

Load

ID,

Max(RowVersion) As RowVersion

From Table.QVD(qvd)

Group by ID;

//The above load will load the max RowVersion for each ID

Left Join (Table)

Load

From Table.QVD(qvd);

Thanks....

Re: Incremental load in Qlikview

stevedark — Tue, 11 Jun 2013 21:49:42 GMT

Hi Dipak,

When the source tables don’t have a last updated date on them it is usually best to create a database trigger to create a separate table with ID and DateUpdated in that can then be joined to when doing the select.

If the majority of rows never get updated in your database you could simply take rows with ID’s later than the last ID pulled from the source database plus any rows with a version greater than one. This would however only work if updates of previous rows were rare (eg. on a general ledger) as you would have to pull all amended rows on every run.

Hope that helps.

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

Re: Incremental load in Qlikview

Wed, 29 Jan 2014 08:13:41 GMT

Hi Vishwaranjan, Vishwaranjan Kumar

Thanks for the post, it is really helpful especially the attachment. Whilest most of the script you posted makes sense I am struggling to understand why you are using the peek command. Could you not just do the follow?

Max:

load max(ID) as maxID

resident Master;

Let vMax = maxID

Drop table Max;

I am just keen to understand why the peek command is needed. I would assume that the maxID would be based on the first load and this value would not change during the incremental load process. In addition without another 'Let' command I would have thought the variable would not update.

I am only asking to increase my understanding, perhaps I am missing something?

Thanks

Dan

Re: Incremental load in Qlikview

stevedark — Wed, 29 Jan 2014 12:31:48 GMT

maxID is a field in that context, not a variable. The PEEK statement interrogates a field, the parameters picking which row to look at, and then outputs a single value.

It can do some clever stuff when used to look at adjacent rows in a load - but you don't need to worry about that to just grab a max ID.

Hope that helps.

Re: Incremental load in Qlikview

dhavalvyas — Mon, 25 Sep 2017 09:54:55 GMT

it is necessary that date format in both date as well as in variable should be same ?

Re: Incremental load in Qlikview

dhavalvyas — Mon, 25 Sep 2017 09:55:39 GMT

because my script showing error for it,so how to overcome this issue ?

Re: Incremental load in Qlikview

stevedark — Mon, 25 Sep 2017 12:05:39 GMT

If the dates are all numeric fields showing as dates (try multiplying them by 1 to check this) rather than text then the formatting shouldn't matter too much in the load script. It can cause problems with Set Analysis though.