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

Only load latest file

I am using the script below for incremental loading of files copied out to a shared drive and it works great.

I want to change this code to only load the latest file for another application I am working on.  Any suggestions how I can determine which file is the most recent?  I did not write this code, I got this from the community some time back.

//Creates current table  of excel files in the directory for incremental loading to compare to existing

TRACE DoDir;

SUB DoDir (Root) 

     FOR Each File in FileList(Root&'$(vSourceFile)') 

     Files: 

     LOAD '$(File)' as File_Name

     AutoGenerate 1; 

   NEXT File 

ENDSUB 

CALL DoDir (vRoot) 

Trace Loop;

FOR i=0 to NoOfRows('Files')-1  

   LET vFileName = Peek('File_Name', 0, 'Files'); 

   IF Date(Date#(SubField(vFileName, '_', 4), 'DD-MMM-YYYY')) >= vDateFrom and Date(Date#(SubField(vFileName, '_', 5), 'DD-MMM-YYYY')) <= vDateTo then 

      AppExtracts: 

      LOAD * 

      From [$(vRoot)\$(vFileName)] ; 

   ENDIF 

NEXT i; 

11 Replies
giakoum
Partner - Master II
Partner - Master II

you can use the filetime function to determine the latest one :

filetime([ filename ])

Returns a timestamp for the date and time of the last modification of the file filename. If no filename is specified, the function will refer to the currently read table file.

Examples:

filetime( 'xyz.xls' )

Will return the timestamp of the last modification of the file xyz.xls.

Load *, filetime() as X from abc.txt ;

Will return the date and time of the last modification of the file (abc.txt) as a timestamp in field X in each record read.

marcus_sommer

You could add filetime() and and use a second load with a sorting-order and use the first or last row or a max-statement about the filetime:

....

Files: 

     LOAD '$(File)' as File_Name, filetime('$(File)') as FileTime

     AutoGenerate 1; 

....

MaxTime:

Load File_Name, max(FileTime) as MaxFileTime Resident Files Group By File_Name;

.....

- Marcus

Not applicable
Author

thank you!  I will try that and use it to sort and use peek function to get last file, will that work?

giakoum
Partner - Master II
Partner - Master II

yes, or top 1 if you order the results correctly

Not applicable
Author

Thank you.  this helped but I only want to load the one record with the latest date not all the files.  I can order desc by file date then pull in only the first record I believe

giakoum
Partner - Master II
Partner - Master II

load top 1 filename

....

order by filetime desc

Not applicable
Author

Thank you all for suggestions. In the end, I convinced them to just save the latest file so we don’t have to worry about this script. But I was able to successfully create the logic to load the last file.

vikasmahajan

can you attach script for the same.

vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
marcus_sommer

Maybe this is helpful: Re: Pick the latest excel file from folders

- Marcus