Incremental Load if Date is not available.

[Ranjanac]

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

[Mark_Little · Accepted solution]

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.

[Ranjanac · Accepted solution]

Okay, Thank you so much  @Mark_Little 🙂

[udit_k]

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;