Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Incremental Load of New Files Only

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

3 Replies
MVP
MVP

Re: Incremental Load of New Files Only

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
Contributor

Re: Incremental Load of New Files Only

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

Re: Incremental Load of New Files Only

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;

Community Browser