Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mhassinger
Contributor

Loading from multiple files - one for each month/year

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,

    X,

    Y,

    Z

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?

1 Solution

Accepted Solutions
jemancilla
New Contributor III

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

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$);

1 Reply
jemancilla
New Contributor III

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

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$);

Community Browser