topic Re: Only load latest file in QlikView

Only load latest file

Thu, 28 Aug 2014 14:54:20 GMT

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;

Re: Only load latest file

giakoum — Thu, 28 Aug 2014 15:02:39 GMT

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

marcus_sommer — Thu, 28 Aug 2014 15:05:24 GMT

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

Re: Only load latest file

Thu, 28 Aug 2014 15:23:07 GMT

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

Re: Only load latest file

giakoum — Thu, 28 Aug 2014 15:26:20 GMT

yes, or top 1 if you order the results correctly

Re: Only load latest file

Thu, 28 Aug 2014 16:26:15 GMT

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

Re: Only load latest file

giakoum — Thu, 28 Aug 2014 20:10:28 GMT

load top 1 filename

....

order by filetime desc

Re: Only load latest file

Tue, 02 Sep 2014 17:38:29 GMT

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.

Re: Only load latest file

vikasmahajan — Fri, 18 Dec 2015 10:31:56 GMT

can you attach script for the same.

vikas

Re: Only load latest file

marcus_sommer — Fri, 18 Dec 2015 10:40:27 GMT

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

- Marcus

Re: Only load latest file

vikasmahajan — Fri, 18 Dec 2015 10:45:40 GMT

Thanks Marcus

Please can you go through my thread Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

Whether I can use this script for my purpose.

Thanks again

Vikas

Re: Only load latest file

marcus_sommer — Fri, 18 Dec 2015 11:35:43 GMT

If you each xls stores as qvd then will be the suggestion from pcammaert suitable. If you stores all xls in one qvd then you need to store the filenames and filetimes within these qvd. You will need in both cases a loop through your folders/files and checking these parameter to skip the files if they are unchanged or to drop and load again these data/files.

- Marcus