Incremental Load from a drop file

[#1]

Good Day All,

I need your help with incremental load. I have successful implemented incremental load before but this is to do with a flat file with a confusing base file that is making it hard for me to complete this task.

Here is an example of the file format which I need to load only the most recent but have reached my wit's end.

I have all these in a QVD that takes about an hour to run and I know that I can reduce the entire load time by Incremental load.

Please, Qlikview Experts help me out.

[marcus_sommer]

You could run a loop over filelist-feature and checking your files with various filefunctions like:

filetime() or date(date#(mid(filebasename(), 11), 'MMYYYY'), 'D/M/YYYY')

See further examples here: Re: Pick the latest excel file from folders

And more informations about incremental loads could you find here: Advanced topics for creating a qlik datamodel.

- Marcus

[maxgro · Accepted solution]

if you only want to load the last gmd.... file

FOR Each File in filelist ('gmidtnf1*.csv')

  F:

  LOAD

  '$(File)' as Name,

     // subfield to delete the .csv

     // right to get the date Apr.......

  Date(Date#(Right(SubField('$(File)', '.', 1), 9), 'MMMDDYYYY')) as FileDate

  autogenerate 1;

NEXT File

F2:

// load the last file

First 1 noconcatenate load * Resident F order by FileDate desc;

vFile= Peek('Name');

trace $(vFile);

T:

load * from [$(vFile)]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

[#4]

Hi Massimo,

Thank you so much for your swift and helpful response. I followed your scripted and got it to load just the most recent file. The only problem I have is that each time I run the script it gave different record sets and gave an error message. It was just once it gave the number of record that matches the number on the file. I don't know if there is anything I can tweak to get to work well since there will be run on access point.

Here is my script and the error message I am getting. How do I handle that. Please, I will appreciate all your help. Thank You!

for each File in filelist (Filepath)
F:
LOAD
'$(File)' as Name,
Date(Date#(Right(SubField('$(File)', '.', 1), 9), 'MMMDDYYYY')) as FileDate

AutoGenerate 1;

NEXT File

F2:
// load the last file
First 1 NoConcatenate load * Resident F order by FileDate desc;
vFile= Peek('Name');
trace $(vFile);

Test:

load * from [$(vFile)](txt, codepage is 1252, embedded labels, delimiter is ',', msq,no eof);

EXIT Script

[maxgro]

I'm not able to reproduce your error

If you add an exit script (underline) do you get the same error?

FOR Each File in filelist ('gmidtnf1*.csv')

  F:

  LOAD

  '$(File)' as Name,

    // subfield to delete the .csv

    // right to get the date Apr.......

  Date(Date#(Right(SubField('$(File)', '.', 1), 9), 'MMMDDYYYY')) as FileDate

  autogenerate 1;

NEXT File

exit script;     // added

F2:

// load the last file

First 1 noconcatenate load * Resident F order by FileDate desc;

drop table F;    // added

vFile= Peek('Name');

trace $(vFile);

T:

load * from [$(vFile)]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

[#6]

Thank  a lot.

I Set ErrorMode = 0 and it handled the unexplainable error.

[#7]

Thanks  a lot.

I Set ErrorMode = 0 and it handled the unexplainable error.