loop through set of files issue

[alec1982]

hi all,

I have set of excel files. each file name has month name and year.

The files are as follow

Data_March_2014

Data_April_2014

Data_May_2014

...

..

.

Data_April_2016

Every month a new file gets generated with the same naming structure.

I need to load all files into one table and also extract the month and year from each file name to add them as columns.

Anyone has a good logic I can use?

[MarcoWedel]

sample files

[MarcoWedel]

Hi,

if you could load a list of file-urls from some source, then one solution might be:

SET LongMonthNames = 'January;February;March;April;May;June;July;August;September;October;November;December';

tabFiles:
LOAD * INLINE [
    file
    http://community.qlik.com/servlet/JiveServlet/download/1037406-225490/Data_March_2014.xlsx
    http://community.qlik.com/servlet/JiveServlet/download/1037406-225489/Data_April_2014.xlsx
    http://community.qlik.com/servlet/JiveServlet/download/1037406-225512/Data_May_2014.xlsx
];

FOR vCounter = 1 to FieldValueCount('file')
  LET vFile = FieldValue('file',vCounter);

  table1:
  LOAD *,
      Month(MonthYear) as Month,
      Year(MonthYear) as Year;
  LOAD *,
      MonthName(Date#(Mid(FileBaseName(),Index(FileBaseName(),'_',-2)+1),'MMMM_YYYY')) as MonthYear
  FROM [$(vFile)] (ooxml, embedded labels, table is Tabelle1);
NEXT vCounter

DROP Table tabFiles; 

hope this helps

regards

Marco