Loading from multiple files - one for each month/year

mhassinger — Thu, 26 Apr 2012 15:00:21 GMT

I'm not sure how to go about this the right way.

I will need to load from a variable number of files that are named something like "QV Data - Jan 2012.xls." There are no fields in the files for the month and year (it's just in the file name), so I will need to be create new fields during load and assign the proper values. Something like (but obviously not static like this!):

LOAD

'Jan' as Month,

'2012' As Year,

FROM [QV Data - Jan 2012.xls];

If it was just looping through files with the same data structure I'd be ok, but I'm not sure how to correctly create the month and year fields - parsing the filename seems like it could be messy. I'm not confident they wouldn't change the filename structure in some way (but they will always have the 'Month Year' convention at the end).

Any ideas?

Re: Loading from multiple files - one for each month/year

jemancilla — Thu, 26 Apr 2012 22:54:04 GMT

 LOAD
      subfield(Subfield(FileBaseName(),' - ',-1),' ',1)  AS Month
     ,subfield(Subfield(FileBaseName(),' - ',-1),' ',2)  AS Year
     ,Date(Date#(Subfield(FileBaseName(),' - ',-1),'MMM YYYY'))  AS Date
     ,X
     ,Y
     ,Z
FROM
     [QV Data*.xls]
     (biff, embedded labels, table is Hoja1$);

topic Loading from multiple files - one for each month/year in QlikView

Loading from multiple files - one for each month/year

Re: Loading from multiple files - one for each month/year