Replace modified records

[#1]

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]

[rubenmarin]

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:

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;

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.