Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Incremental Load of New Files Only

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

3 Replies
swuehl
MVP
MVP

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

ger_alegria
Partner - Creator
Partner - Creator

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.

Not applicable
Author

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 (qvd);

//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;