Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

leomathew
New Contributor III

how to Load only the latest files?

Hi All,

i need some help for the following.

i recieve data as excel sheets on monthly basis that have name in the followong format.

YYYYMMDD_data.xls

now all these files are saved in a specific folder. While loading the data i want to load the latest 2 files. e.g. if i have the following files

20130301_data.xls

20130201_data.xls

20130101_data.xls.

20121201_data,xls

than only the 20130301_data.xls and 20130201_data.xls should be loaded?

3 Replies
mov
Esteemed Contributor III

Re: how to Load only the latest files?

It could be something like this (please test, I can miss/mistype):

Files_src:
LOAD FileName() as File 
FROM [*.xlsx] (ooxml, explicit labels, table is [Sheet1]);

File:
LOAD DISTINCT
File,
subfield(File, '_', 1) as FileDate
RESIDENT Files_src
DROP TABLE Files_src;

LatestFiles:
FIRST 2
LOAD DISTINCT
File as LatestTwoFiles
RESIDENT File
ORDER BY FileDate DESC;
DROP TABLE File;

LET LatestFile1 = peek('LatestFile',0,'LatestFiles');
LET LatestFile2 = peek('LatestFile',1,'LatestFiles');
DROP TABLE LatestFile;

//-------------------------------

Data:
LOAD
...
RESIDENT FROM [$(LatestFile1).xlsx] (ooxml, explicit labels, table is [Sheet1]);
LOAD
...
RESIDENT FROM [$(LatestFile2).xlsx] (ooxml, explicit labels, table is [Sheet1]);

Hope it helps.

Regards,
Michael

Edit: In fact, I used this approach recently, except I needed only one latest file.

leomathew
New Contributor III

Re: how to Load only the latest files?

Looks Good Michael. i will try this

autopilot
Contributor III

Re: how to Load only the latest files?

Try this. Copy and paste into test qvw application and set folder reference accordingly. Attached in text file also for reference.

set folder = "E:"    // set the folder to the desired file location - leave off everything following \filename.file-extension

;

for each file in filelist('$(folder)\*.xlsx')

FileInfo:

Load

Distinct

FileName() as file_name,

FileBaseName() as file_base_name,

FilePath() as file_path,

FileTime() as file_time

from

$(file)

(ooxml, embedded labels, table is Sheet1)    // change if needed for different table file format

;
next file
;

Qualify *

;

LatestFiles:

First(2)  // set this number to the number of recent files that you want to load

Load *

resident FileInfo

order by file_time DESC

;

Drop Table FileInfo

;

UNQUALIFY *

;

Let source = fieldname(3, 'LatestFiles')

;

Let source_i = fieldvalue('$(source)',1)

;

For i = 1 to NoofRows('LatestFiles')

[File$(i)]:

Load *,

'$(source_i)' as $(source)

from

$(source_i)

(ooxml, embedded labels, table is Sheet1)    // change if needed for different table file format

;

Let source_i = fieldvalue('$(source)',$(i)+1);

;

next i

exit script

;

Community Browser