Incremental load in Qlikview

[#1]

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

[sujeetsingh]

Hi,

Go to this link for concept of Incremental Load

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

[#3]

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

[#4]

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.

[amars]

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

[#6]

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

[amars]

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

[stevedark · Accepted solution]

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/

[#9]

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

[stevedark]

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.