Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
To summarize, what is the way to perform an Incremental Load on a folder full of csv files without Loading the data against the 'Where" condition?
I have a folder with daily files going back 10 years+. To save time, I ran an initial load and Stored everything into a QVD file.
Moving forward, I would like to add data from only New files, but on an ad-hoc basis.
When running an Incremental Load, I do not want to open ALL files to match the "Where" statement. I've set an artificial variable limit (Year portion of the Last_Modified_Date 2015) with a wildcard into the file name, so the Load would only look into the current year. This will be problematic at year-end.
The problem I'm trying to overcome is that, say after 200 days, that is 200 files to load for a match to my Where statement. This takes quite a bit of time. Is there a way to perform an Incremental Load on a folder full of CSV files without Loading the data against the 'Where" condition?
File Names have have a date stamp in the file name.
I'm a new QlikView user! A code example would help.
Many thanks!
You may consider moving the files to a different folder after loading and storing to your QVD.
Or create a loop of the files and only load the newest files
FOR Each File in filelist (Root&' \*.' &Ext)
Let vCheck = ... // Parse the date from file name and compare to latest Date from QVD
IF vCheck THEN
LOAD Field1,
Field2,
...
FROM $(File);
ENDIF
NEXT File
You may consider moving the files to a different folder after loading and storing to your QVD.
Or create a loop of the files and only load the newest files
FOR Each File in filelist (Root&' \*.' &Ext)
Let vCheck = ... // Parse the date from file name and compare to latest Date from QVD
IF vCheck THEN
LOAD Field1,
Field2,
...
FROM $(File);
ENDIF
NEXT File
You can use a variable with the actual date for loan only the file of this date:
Let vActualDate=today();
Table:
LOAD
*
FROM
[..\DATA\FILENAME_$(vActualDate).csv];
Store Table into ..\QVD\ActualQVD.qvd;
and after that you can do a incremental load
Master:
LOAD
*
FROM
[..\QVD\HistoricalQVD.qvd];
concatenate(Master)
LOAD
*
FROM
[..\QVD\ActualQVD.qvd];
Let me know if this help you.
Ger.
Thank you! I pursued swuehl second option. Script execution is significantly faster, but I must admit the script takes somewhat longer than I anticipated to pull the file names from my network drive.
My code for reference:
//Loading Data from QVD
[DATA_TABLE]:
Load *
FROM
//Find Last Modified Date
Last_Updated_Date:
LOAD Max(Modified_Date) as MaxDate
resident [DATA_TABLE];
//Store Last Modified Date to a Variable
LET Last_Updated_Date = peek('MaxDate',0,'Last_Updated_Date');
LET vStore='No';
FOR Each vFile in FileList('C:\ABC\TOR*.csv')
LET vfilenamedate = Date#(mid('$(vFile)',23,8), 'YYYYMMDD');
IF (vfilenamedate > Last_Updated_Date) then
LET vStore='Yes';
Concatenate
LOAD *
FROM
[$(vFile)];
ENDIF;
NEXT vFile;
// Replace Old QVD file
IF (vStore = 'Yes' ) then
Store [DATA_TABLE] into c:/blah.qvd (qvd);
ENDIF;