topic Re: Load Latest folder files in QlikView

Load Latest folder files

Mon, 01 Feb 2016 12:01:38 GMT

Hello Community,

I need your help in the below scenario:

I have subfolders in QVData folder as below and in the same order

BD-4

BD-3

BD-2

BD-1

BD1

BD2

BD3

BD4

At first I will have BD-4 folder only available, but after interval of time, another folder named BD-3 will be created and so on.

I need to old .XLS files from only the Latest folder available as per the order of folders above.

Thanks in advance,

Singh

Re: Load Latest folder files

Anonymous — Mon, 01 Feb 2016 12:04:37 GMT

Did you check this?

Load latest Excel file to Qlikview

how to Load only the latest files?

Re: Load Latest folder files

HirisH_V7 — Mon, 01 Feb 2016 12:15:38 GMT

Hi,

Check this ,

This code will load three year excel files based on file name ,You can modify your code based on your requirement,

LET vFilePath = 'Your Path'; 

FOR EACH file in FileList('$(vFilePath)\File*.xlsx'); 
LET vFileYear =  Left(Right(file, 9), 4);   

LET vMaxYear1 = (Rangemax(vFileYear, vMaxYear1))-1;
LET vMaxYear2 = (Rangemax(vFileYear, vMaxYear2))-2;
LET vMaxYear  = (Rangemax(vFileYear, vMaxYear)); 

Next

Data: 
LOAD * 
FROM 
[$(vFilePath)\Eng-$(vMaxYear1).xlsx]   
(ooxml, embedded labels, table is Data);

LOAD * 
FROM 
[$(vFilePath)\Eng-$(vMaxYear2).xlsx]   
(ooxml, embedded labels, table is Data);


LOAD * 
FROM 
[$(vFilePath)\Eng-$(vMaxYear).xlsx]   
(ooxml, embedded labels, table is Data);

HTH,

Hirish

Re: Load Latest folder files

yura_ratu — Mon, 01 Feb 2016 17:35:18 GMT

Hi Angad,

To get subfolders list from a folder, you can use this script

Let _Root = 'ROOT FOLDER PATH';

For Each Dir in DirList ('$(_Root)'&'\*')
Directories:
LOAD
'$(Dir)' as DirectoryPath,
SubField('$(Dir)','\',SubStringCount('$(Dir)','\')+1) as DirectoryName
AutoGenerate 1;
NEXT Dir

Then find last one (use any other function to get max value from folders list)

LastDirectory:
 LOAD
 MaxString(DirectoryName) as LastDirectory
 Resident Directories
 ;
 LET vLastDirectory = Peek('LastDirectory',-1,'LastDirectory');
 DROP Table LastDirectory;

And Finally load files from this folder

For Each File in FileList ('$(_Root)'&'$(vLastDirectory)'&'\.xls')
Data:
LOAD *
From $(File) (ooxml, embedded labels, table is Sheet1) // edit it, if needed
;
Next File

I haven't test it properly, so I couldn't work as it is.