Crying for help ********

[#1]

Hi All,

I have few  Spread sheets(2011- 2013)  in drop box , each spread sheets have 5 sheets in it ,My Requirement is to load latest spread sheet and thier  followoing sheets.

assumption: Let us assume we are in Aug 2013

For eg;

we should load the below mentioned spread sheets  in qlikview from drop box  if we are in year Aug 2013

Global inventory by business 2011 M12 (Latest: where M12 = M Dec)

Global inventory by business 2012 M12 (Latest where M12= M Dec)

Global Inventory by business 2012 M 8 (Latest where M 8= M Aug)

To achieve this i need a script with a result of field having field values  as Latest file names  from existing spread sheets

  Latest files name

Global inventory by business 2011 M12

Global inventory by business 2012 M12

Global inventory by business 2013 M 8

Earlier it used to achieve by loading XML file .

[#2]

Please find Attachment.

[Anonymous]

This may help you.  Maybe it is not precise, but should be close enough so you can figure out:

// Find the files to load ------------------------------------
Files_src:
LOAD FileName() as FileName 
FROM [\\<path>\*.xlsx] (ooxml, explicit labels, table is <...>);

File:
LOAD
FileName,
TextBetween(File, 'Global Inventory By Business ', ' M') as FileYear,
TextBetween(File, 'M', '.') as FileMonth
RESIDENT Files_src;
DROP TABLE Files_src;

INNER JOIN (File)
LOAD
FileName,                                        // here are the files you need
max(FileMonth) as FileMonth
RESIDENT Files_tmp
GROUP BY FileYear;

// Load data from  files ------------------------------
LET noRows = NoOfRows('File')-1;  // number of files to load

FOR i=0 to $(noRows)
LET f=peek('FileName',$(i),'File');
Data:
LOAD
  ...
FROM [\\<path>\$(f)] (ooxml, explicit labels, table is <...>);
NEXT

DROP TABLE File;
LET i = null();
LET f = null();

Regards,

Michael