Incremental Load to append data

[loganathan]

Hi All,

This is my sample table. I have to do incremental load on this just to concatenate data whenever i have new 'CreateUtcTime'. Please suggest something with sample script.

TIA

[Clement15]

Hello,

Could a code like this meet your need?

table:
Load
*
from [H:\Master\*.qvd] (qvd);

Concatenate

table:
LOAD
CreateUtcTime,
FileBaseName( ) as TableName,
NoOfRecords
FROM [H:\Date\*.qvd]
(XmlSimple, table is QvdTableHeader)
where not Exists(CreateUtcTime);

STORE table INTO [H:\Master\*.qvd] (QVD);
DROP TABLE table;

[loganathan]

Hi,

Thanks for your time. But this wont help in my scenario. Basically I wanted to consider max of 'CreateUtcTime' from the existing QVD and load new ones whatever greater than that to concatenate.

[Clement15]

Hello,
Something like this should work then right?

table :
Load
*
from [H :\Master\*.qvd] (qvd) ;

Temp :
Load
max(CreateUtcTime) en tant que CreateUtcTime
Ordre de la table
résidente par CreateUtcTime ;

LET vMaxCreateUtcTime = Num(Peek('CreateUtcTime',-1,'Temp'));

Drop table Temp;

Concatenate(table)

table :
LOAD
CreateUtcTime,FileBaseName
( ) as TableName,NoOfRecords

 FROM [H :\Date\*.qvd]
(XmlSimple, table is QvdTableHeader)
where CreateUtcTime> $(vMaxCreateUtcTime) ;


STORE table INTO [H:\Master\*.qvd] (QVD);
DROP TABLE table;

[marcus_sommer]

As far as your incremental-key-values (in your case the timestamp) are unique the above suggested approach should be suitable. If this couldn't be ensured against a single field the combination with n other fields might create such unique key - in the above case it might be:

CreateUtcTime & '|' & TableName as Key

Nevertheless the max. timestamp could be queried and used as filter, maybe in this way:

let vWhere = if(filesize('History.qvd'), vMax, 0);

Current: load * from Source where CreateUtcTime > $(vWhere);

Max: load max(fieldvalue('CreateUtcTime', recno()) as Max
autogenerate fieldvaluecount('CreateUtcTime');

let vMax = peek('Max', 0, 'Max');

if filesize('History.qvd') then
   concatenate(Current) load * from History.qvd;
end if

store Current into History.qvd;