Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
As we know, incremtal load perform on "Dates".If we do Not have "Date" field in our table. How incremental load will perform??
For example: Stationary table
Pid,pname,quantity
1,book,6
2,sharpener,9
3,eraser,9
Now every one hour new product will get added.
▪️new records inserted▪️
Pid,pname,quantity
4,markers,10
5,higlighters,9
▪️Delete records▪️
Pid,pname,quantity
1,book,6
And, stationary table does not have any date field.
So here,how incremental load be perform here??
Hi,
My understanding is an incremental load will just load where something is greater than. So in theory will work with any number.
You should just be able to load the max Pid from your existing data and then load where Pid is Greater than that from your new data.
Okay, Thank you so much @Mark_Little 🙂
Hi,
My understanding is an incremental load will just load where something is greater than. So in theory will work with any number.
You should just be able to load the max Pid from your existing data and then load where Pid is Greater than that from your new data.
Okay, Thank you so much @Mark_Little 🙂
We could get incremental load with insert/update/Delete following below code for your example:-
/*********************RawData**********************/
Data:
Load *,
'$(FLagVlaue)' as Flag,
Timestamp('$(VLastime)') as Lastime;
Load * Inline
[
Pid,pname,quantity
1,book,6
2,sharpener,9
3,eraser,9
];
Store Data into [lib://DataFiles/Data.qvd](qvd);
//concatenate
let VLastime=(QvdCreateTime('lib://DataFiles/Data.qvd'));
let vLAst=peek('Lastime',0,'Data');
let FLagVlaue='OLD';
Let Flag='New';
//NoConcatenate
/*****************************Insert only new records from source**********************************/
// Qualify*;
InsertData:
Load *,
timestamp('$(VLastime)') as NewTimestamp,
'$(Flag)' as Flag;
load
* Inline
[
Pid,pname,quantity
4,markers,10
5,higlighters,9
6,compass,11
];
Concatenate(Data)
Load
NewTimestamp as Lastime,Flag,
Pid,pname,quantity
Resident InsertData
where NewTimestamp>='$(vLAst)';
Drop table InsertData;
// /*************************************Update new record & Delete old record*****************************/
//Qualify *;
NoConcatenate
UpdateData:
load *,
timestamp('$(VLastime)') as Lastime,
'$(Flag)' as Flag,
Pid as NewPid
;
;
load
* Inline
[
Pid,pname,quantity
1,book,6
];
NoConcatenate
NEwData:
Load
*
resident
Data
where not exists(NewPid,Pid);
drop table Data;
Concatenate(NEwData)
Load
Lastime,Flag,
Pid,pname,quantity
Resident UpdateData;
Drop table UpdateData;