Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

andreyfcdk91
New Contributor III

Load latest files from directory

Good day!

During working in script with loading xlsx files, i've met problem with reading only latest files.

In script i have such block, which read files which have updating date=today()

Directory;

FOR Each File in filelist ('$(DataPath)Test_*.xlsx')

  Main_Temp:

FROM

  '$(File)'

(ooxml, embedded labels, table is Sheet1)

Where date(FileTime( '$(File)' ))=Today();

NEXT File;

But this code read all files and get values from file which have updating date=today() and that's why time for this load is very long.

Is there any way to modify this code to read only files with updating date=today()  ? (In my directory i have only one such file with one record and i think, time for it load must be very short)

Thanks.

1 Solution

Accepted Solutions

Re: Load latest files from directory

Filetime is a timestamp and needed to be floored like and NEXT need no counter and ;

FOR Each File in filelist ('$(DataPath)Test_*.xlsx')

if date(floor(FileTime( '$(File)' )))=date(Today()) then

      Main_Temp:

     FROM

       '$(File)'

      (ooxml, embedded labels, table is Sheet1)

end if

NEXT File;

- Marcus

5 Replies

Re: Load latest files from directory

Try:

FOR Each File in filelist ('$(DataPath)Test_*.xlsx')

if date(FileTime( '$(File)' ))=date(Today()) then

      Main_Temp:

     FROM

       '$(File)'

      (ooxml, embedded labels, table is Sheet1)

end if

NEXT File;

- Marcus

hirishv7
Honored Contributor

Re: Load latest files from directory

Hi

Check this,

Directory;

FOR Each File in filelist ('Test_data*.xlsx')

Main_Temp:

  load

  '$(File)' as Name,

FileTime( '$(File)' ) as FileTime,

  Week,

  Sales

FROM

  '$(File)'

(ooxml, embedded labels, table is Sheet1);

NEXT File;

Table:

first 1

Load

Name,

FileTime,

  Week,

  Sales,

  1 as dummy

  Resident Main_Temp

  Order By FileTime asc;

drop table Main_Temp;

NoConcatenate

Excel:

load

  '$(File)' as Name,

FileTime( '$(File)' ) as FileTime,

  Week,

  Sales

FROM

  '$(File)'

  (ooxml, embedded labels, table is Sheet1);

Picking the latest file based on the Filetime().

HTH,

PFA,

Hirish

andreyfcdk91
New Contributor III

Re: Load latest files from directory

doesn't works(

this code doesn't read any file..

Re: Load latest files from directory

Filetime is a timestamp and needed to be floored like and NEXT need no counter and ;

FOR Each File in filelist ('$(DataPath)Test_*.xlsx')

if date(floor(FileTime( '$(File)' )))=date(Today()) then

      Main_Temp:

     FROM

       '$(File)'

      (ooxml, embedded labels, table is Sheet1)

end if

NEXT File;

- Marcus

andreyfcdk91
New Contributor III

Re: Load latest files from directory

Works)))

Thanks, dear!!!

Community Browser