Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Incremental Load from a drop file

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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

View solution in original post

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

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

Not applicable
Author

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

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

Not applicable
Author

Thank  a lot.

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

Not applicable
Author

Thanks  a lot.

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

barish
Contributor
Contributor