Replace modified records

Tue, 02 Dec 2014 15:50:28 GMT

Hi,

I'm a total noob when it comes to incremental loads. The insert part works fine (see below script) however, on a daily baisis I receive reports with updated rows (always the same reference though).

So what I need is.

When a reference already exists (on other words the new report comes with an update). Then the old record with this reference should be replaced with the latest record (latest TimeStamp).

Any suggestions how to approach this?

Kind Regards,

Olle

LOAD currency as CCY,
     [account no.] as ACC,
     [value date] as ValueDate,
     LEFT([CR/DR],1) as [D/C],
     IF(LEFT([CR/DR],1)='D',amount*-1,amount) as Amount,
     reference as REF,
     type as Type,
     ,FileName() as Source

FROM [01.Raw_Data\*.xls]
(biff, embedded labels, header is 3 lines, table is @1)Where Len(currency)=3;

DateTimeInfo:
LOAD
     Timestamp#((DATE(@1, 'MM/DD/YYYY')&' '&TIME(@2, 'hh:mm:ss')),'DD/MM/YYYY hh:mm:ss') As TimeStamp
     ,FileName() as Source

FROM [01.Raw_Data\*.xls]
(biff, no labels, header is 1 lines, table is @1) Where RecNo() < 2;

JOIN(Table1)
LOAD * RESIDENT DateTimeInfo;
DROP TABLE DateTimeInfo;

STORE Table1 into [03.Final_Data\Data.qvd]

Re: Replace modified records

rubenmarin — Tue, 02 Dec 2014 16:24:50 GMT

Hi Olle, to do an incremental load with update you'll need a unique key that identifies each record, supposing reference is that key and Raw_Data contains only the updated records.

The logic is to first load the updated records and then load from qvd the records not updated, something like:

Table1:

Concatenate (Table1)

LOAD *

FROM [03.Final_Data\Data.qvd] Where not exist(REF, REF);

If you receive files with updated and not updated records then you need to first load the records updated based on the last execution time or the max(modifiedDateTime) stored in qvd.

Hope this helps.

topic Replace modified records in QlikView

Replace modified records

Re: Replace modified records