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.
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.
