Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello dears.
i implemented Incremental Load (Insert ,Update ,Delete)
i have a problem .
for the first time , i created qvd File. (i don't problem)
let vQVDExistsFile=isnull(QvdCreateTime('$(vSourceQvdFile)SaleQVD.qvd'));
if vQVDExistsFile=-1 then
Sale:
LOAD OrderId,
ProductId,
CustomerId,
Amount,
Date_Updated,
Num(Date_Updated) as NumDate
FROM
$(vSourceData)Sale.xlsx
(ooxml, embedded labels, table is Sheet1);
Store Sale into $(vSourceQvdFile)SaleQVD.qvd(qvd);
END IF
I found that change records for the second time
But i Can not Append into SaleQVD.qvd
All New Records (Update,Delete) Override into SaleQVD,Not Append
There is no append option when you write to a QVD file. It will always have to be rewritten in full. That is why you have to do the appending in-memory and concatenate the old data with you new data. So you have the old data stored in a QVD and new data has to be read from another source and concatenated with the table that was read from the QVD.
You have to have some statements between the LOAD .... (qvd) and the Store. Which should look a bit like this:
CONCATENATE LOAD (Sale)
*;
SQL
.....
;
or
CONCATENATE LOAD (Sale)
*
FROM filesource.txt (txt);
hi leila,
The next case is applicable when data in previously loaded records may have changed between script executions. The following conditions apply:
Script Example:
QV_Table:
SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
WHERE ModificationTime >= #$(LastExecTime)#;
Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT Exists(PrimaryKey);
STORE QV_Table INTO File.QVD;
Regards
Neetha
hello peter
Can you see Attachment File.?
I've done exactly that
hello neetha
Can you see Attachment File.?
I've done exactly that
hi leila
please paste the full code here,mine is personnel edition
set vSourceQvdFile='QVDFile\';
set vSourceData='Source\';
let vQVDExistsFile=isnull(QvdCreateTime('$(vSourceQvdFile)SaleQVD.qvd'));
if vQVDExistsFile=-1 then
Sale:
LOAD OrderId,
ProductId,
CustomerId,
Amount,
Date_Updated,
Num(Date_Updated) as NumDate
FROM
$(vSourceData)Sale.xlsx
(ooxml, embedded labels, table is Sheet1);
Store Sale into $(vSourceQvdFile)SaleQVD.qvd(qvd);
EXIT Script;
END IF
NoConcatenate
Sale:
LOAD OrderId,
ProductId,
CustomerId,
Amount,
Date_Updated,
NumDate
FROM
$(vSourceQvdFile)SaleQVD.qvd(qvd);
NoConcatenate
SaleOrder:
Load Max(NumDate) as MaxDate
Resident Sale;
let vMaxUpdateDate=peek('MaxDate',0,'SaleOrder');
Drop Table SaleOrder;
NoConcatenate
IncrementalLoad:
LOAD * where NumDate>$(vMaxUpdateDate);
LOAD *,Num(Date_Updated) as NumDate;
LOAD OrderId,
ProductId,
CustomerId,
Amount,
Date_Updated
FROM
$(vSourceData)Sale.xlsx
(ooxml, embedded labels, table is Sheet1)
;
Concatenate
LOAD OrderId,
ProductId,
CustomerId,
Amount,
Date_Updated,
NumDate
FROM
$(vSourceQvdFile)SaleQVD.qvd(qvd)
where not Exists(OrderId);
CountOfRecords:
Load count(OrderId) as CountOrderId
Resident IncrementalLoad;
let vCountofRecords=Peek('CountOrderId',0,'CountOfRecords');
if vCountofRecords<>0 then
store IncrementalLoad into $(vSourceQvdFile)SaleQVD.qvd(qvd);
//store IncrementalLoad into $(vSourceQvdFile)Sale1.qvd(qvd);
end if
//Drop table IncrementalLoad;
Is it really useful to do an incremental load from an Excel file? How many rows does the original Excel contain?
Usually an incremental load is performed in order to save both time and resources. But your scripts reads the full Excel file anyway, sometimes twice (the very first time)...