Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
Go to this link for concept of Incremental Load
http://www.quickintelligence.co.uk/qlikview-incremental-load/
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
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.
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
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
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....
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
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
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.