topic Re: Incremental Load to append data in App Development

Incremental Load to append data

loganathan — Thu, 05 Dec 2024 09:16:51 GMT

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

Re: Incremental Load to append data

Clement15 — Thu, 05 Dec 2024 10:35:17 GMT

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;

Re: Incremental Load to append data

loganathan — Thu, 05 Dec 2024 12:22:31 GMT

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.

Re: Incremental Load to append data

Clement15 — Thu, 05 Dec 2024 13:27:07 GMT

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;

Re: Incremental Load to append data

marcus_sommer — Thu, 05 Dec 2024 13:43:29 GMT

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;