Incremental Load (Insert and Update)

LoKi_asterix — Wed, 23 Apr 2025 10:52:01 GMT

Hi Qlik Community,

I found a document outlining a basic incremental load scenario, but I'm having trouble replicating it. Getting this error;

OleDb error

Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Conversion failed when converting date and/or time from character string.

SQL SELECT *
FROM ORACLE.dbo.BalTransValuedated
WHERE VALUE_DATE >= '0'
AND VALUE_DATE < '4/23/2025 11:01:07 AM'

@marcus_sommer @Oleg_Troyansky @marksouzacosta

//Actaul Script

LET vQVDPath = 'D:\DailyBalances'; // Set QVD storage Directory
LET vExecTime = GMT();
SET vLastExecTime = 0 ; // resetting vLastExecTime

// As long as a QVD already exists, find the latest timestamp
// for modified records. This will be used to generate the delta set.
if not isnull(QVDCreateTime('$(vQVDPath)\BalTransValuedated.qvd')) then

LoadTime:
LOAD Max(VALUE_DATE) as VALUE_DATE
FROM $(vQVDPath)\BalTransValuedated.qvd (qvd);

Let vLastExecTime = Date(Peek('VALUE_DATE', 0, 'LoadTime'),'YYYY-MM-DD');

Drop Table LoadTime;

end if

SQL SELECT *
FROM ORACLE.dbo.BalTransValuedated
WHERE VALUE_DATE >= '$(vLastExecTime)'
AND VALUE_DATE < '$(vExecTime)';

// Check to see if this is the first reload. If it is, skip this step
if Not isnull(QvdCreateTime('$(vQVDPath)BalTransValuedated.qvd')) then
Concatenate (BalTransValuedated)
LOAD *
FROM $(vQVDPath)BalTransValuedated.qvd (qvd)
WHERE Not(Exists(AC_ID));
end if

// If data exists within table, store to QVD.
if NoOfRows('BalTransValuedated') > 0 then
STORE BalTransValuedated INTO $(vQVDPath)\BalTransValuedated.qvd;
Drop Table BalTransValuedated;
end if

Re: Incremental Load (Insert and Update)

marcus_sommer — Wed, 23 Apr 2025 11:06:24 GMT

It's a sql-error from your data-base because the date-filter conditions aren't correct. The specified format and syntax here:

VALUE_DATE < '4/23/2025 11:01:07 AM'

may be the right one but it could be not '0' at the same time.

Just comment the incremental part and the where-clause to see how this date-field is look like. After that set a few timestamp-values as condition to see if it returned no errors and the expected periods. After that you could transfer the format/syntax again to the incremental part.

topic Incremental Load (Insert and Update) in App Development

Incremental Load (Insert and Update)

Re: Incremental Load (Insert and Update)