Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Honored Contributor II

Re: Only load latest file

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.

Re: Only load latest file

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

Re: Only load latest file

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

giakoum
Honored Contributor II

Re: Only load latest file

yes, or top 1 if you order the results correctly

Not applicable

Re: Only load latest file

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

Highlighted
giakoum
Honored Contributor II

Re: Only load latest file

load top 1 filename

....

order by filetime desc

Not applicable

Re: Only load latest file

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
Honored Contributor III

Re: Only load latest file

can you attach script for the same.

vikas

Re: Only load latest file

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

- Marcus

Community Browser