topic Re: Crying for help ******** in QlikView

Crying for help ********

Mon, 16 Dec 2013 18:23:52 GMT

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 .

Re: Crying for help ********

Mon, 16 Dec 2013 18:25:23 GMT

Please find Attachment.

Re: Crying for help ********

Anonymous — Mon, 16 Dec 2013 19:16:37 GMT

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