topic Loading the latest file in QlikView

Loading the latest file

Wed, 25 Feb 2015 16:04:31 GMT

I have an automatically generated files with changing names let’s say: file_name_123756, file_name_544451, file_name_654865 and so on. I would like to load only the latest file (today's file). The load looks like that :

Directory;
LOAD Company,
Ledger,

[Doc Id],
[Doc Number],
[Doc Date],
[Due Date],
[Line Debt],
[Total Exc Unallocated Cash],
[<90days],
[>90days],
[Not Yet Due],
[0-30],
[31-60],
[61-90],
[91-120],
[121-150],
[151-180],
[181-365],
[365+]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 8 lines);

Have you got any suggestions how to do it?

Re: Loading the latest file

MK_QSL — Wed, 25 Feb 2015 16:24:47 GMT

FileName:

LOAD

SubField(SubField(FileName(),'_',3),'.',1) as NumberPortion

FROM

(ooxml, embedded labels, table is Sheet1);

Temp:

Load Max(NumberPortion) as MaxNum Resident FileName;

Drop Table FileName;

Let vMaxNum = Num(Peek('MaxNum',0,'Temp'));

Table:

Load

From

(ooxml, embedded labels, table is Sheet1);

Re: Loading the latest file

Michiel_QV_Fan — Wed, 25 Feb 2015 16:32:06 GMT

Based on the file date you can achive the same:

for each file in filelist('C:\Lokale bestanden\Kilometers\*.xls')

KM_bestanden:

load '$(file)' as Bestand,

FileTime('$(file)') as Bestand_tijd

AutoGenerate (1);

let latest_file = peek('Bestand',-1 , 'KM_bestanden');

KM_laatste:

NoConcatenate

load *

Resident KM_bestanden

where Bestand = '$(latest_file )';

drop table KM_bestanden;

Re: Loading the latest file

Anonymous — Wed, 25 Feb 2015 16:48:02 GMT

Create Batch File last.bat:

echo @off

for /f "tokens=1* delims=:" %%a in ('dir *.xls/o:-d/b ^| findstr /n .') do if %%a leq 1 echo LET LAST=%%b>last.qvs

on Edit Scritpt:

EXECUTE cmd.exe /C last.bat

$(must_include=last.qvs);

Use var LAST created

Re: Loading the latest file

Anonymous — Wed, 25 Feb 2015 16:54:52 GMT

Hi,

Please try:

TEMP:
LOAD Distinct FilePath() AS FileName,
FileTime() AS FileTimeStamp
From ;

TempTable:
First 1
load *
resident TEMP
order by FileTimeStamp desc;

let vLatestFile = peek('FileName',0,'TempTable');

LOAD *
From '$(vLatestFile)'
(ooxml, embedded labels, table is Sheet1);

Regards

Neetha

Re: Loading the latest file

Mon, 20 Apr 2015 14:03:55 GMT

Thank you for that Manish it worked well!

My only concern is that I have a lot of files in the folder and it takes a lot of time to reload.

Is there any way to reload it quicker??