topic Re: Load latest files from directory in QlikView

Load latest files from directory

Anonymous — Thu, 21 Jan 2016 14:45:04 GMT

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.

Re: Load latest files from directory

marcus_sommer — Thu, 21 Jan 2016 14:47:50 GMT

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

Re: Load latest files from directory

HirisH_V7 — Thu, 21 Jan 2016 14:57:57 GMT

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

Re: Load latest files from directory

Anonymous — Thu, 21 Jan 2016 16:36:02 GMT

doesn't works(

this code doesn't read any file..

Re: Load latest files from directory

marcus_sommer — Thu, 21 Jan 2016 17:05:13 GMT

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

Re: Load latest files from directory

Anonymous — Thu, 21 Jan 2016 17:13:06 GMT

Works)))

Thanks, dear!!!