Incremental load in Extraction or Transformation

[Kohli]

I have XL file with 10 Sheets. I have created 10 Qvd's in Extraction. i have to create a incremental Data for this. How can i will. Can i write the script in Extraction or Transformation.

[ogster1974]

For incremental load to work you need 2 things

1. A unique key for each QVD.. This can be an id or concatenated field but must be unique

2. A way to id the record has changed usually a date modified field.

[Kohli]

Yes, i have unique key and created qvd, In extraction only i have to write the script or in Transformation..

[Mark_Little]

Hi

I would advise that it is added at the extraction layer.

This is because this is normally the slow part of a load, also say if you were loading from a databases what could impact the end user.

Mark

[kingsleyh]

Hi X.

LET vRunTime = 0;      // remove this after your first reload

LET vLatestFileList = ;

For each File in FileList('C:/etc/*.qvd')

     If FileTime('$(File)')> $(vRunTime)

          LET vLatestFileList = '$(vLatestFileList )' & '$(File)';

     Endif;

Next File;

//Now you have a list of the latest files in the variable vLatestFileList - which looks like this 'C:/etc/abc.qvd','C:/etc/def.qvd'

//I would load these tables individually if there aren't too many qvds (the reason for this is so you can create UIDs for each and rename the tables to something meaningful)

//Let me know if you need help renaming them dynamically

For each File in $(vLatestFileList)

     ABC:               //Please note, I am putting ABC here as an example so that you can see what to concatenate to (but this name should be something unique for each table)

     Noconcatenate

     LOAD

     *,

     ID & '|' & Category as UID1        //EG. Get a unique ID,

     [$(File)] (qvd);

Next File;

Concatenate(ABC)

LOAD *

(qvd)

Where not exists(UID1, ID & '|' & Category);

LET vRunTime = Now();                                   //This stores the time of when that script finishes

Essentially what is happening is:

Find all your latest Files

Load them

Load your old files (Where the Unique ID does not match any Unique ID's from the latest Files) and concatenate the 2 tables

Please note that this is assuming you don't have a modified date in your data, if you have a modified date, then you would just load all the qvds where each modified date is >= your last run time (vRunTime).

This is also assuming the qvd's won't be updated or changed unless the source changes.

Many thanks

Kingsley