topic Re: Making Month/Year Date From Source File Name in QlikView

Making Month/Year Date From Source File Name

Wed, 07 Aug 2013 10:42:41 GMT

Hi There,

I have a number of source files and they are labeled 01-13_MUV.xlsx.

The 01 is the month and 13 is the year.

I want to extract this month year date to create a listbox filter of month year within my app.

I am wondering if this is possible, or will have to just add the month/year column into the actual source file.

I would greatly appriciate any help I can get on this.

Thanks

Re: Making Month/Year Date From Source File Name

Anonymous — Wed, 07 Aug 2013 11:06:52 GMT

You can loop through the files, assigning the filenames to a variable, which you would then need to use in the load statement and can also strip apart to use for making fields. Something like this:

SET Dir = 'C:\ExcelFilesDir';

FOR EACH File IN FileList($(Dir)&'*.xlsx')

SET vExcelFileMonth = Left($(File),2);

SET vExcelFileYear = Mid($(File),4,2);

TableName:

LOAD

MakeDate($(vExcelFileYear),$(vExcelFileMonth)) AS ExcelFileDate

FROM [$(File)] (biff, embedded labels, table is [Sheet1$]);

Jonathan

Re: Making Month/Year Date From Source File Name

richard_pearce6 — Wed, 07 Aug 2013 11:07:31 GMT

Are you loading the tables with a wildcard or are you using a loop?

I'd use a loop and create the filename using variables, checking the file exists and loading each one. From there it's pretty straight forward to add the field into the table using the same variables you used to load it.

Re: Making Month/Year Date From Source File Name

Wed, 07 Aug 2013 11:15:21 GMT

Im loading the files using *.

I just clicked into the table files and brought in the source file, and the changed the 01-13 to *,

so it looks like this;

From

[MUV analysis\ *.xlsx]

Where when I load one source file at a time it looks like this

From

[MUV analysis\01-13.xlsx].

I need to somehow extract the 01-13 and make it into a MonthYear field in the app, but im not sure can that be done when im using * to load in all the files?

Note: Each source file has the exact same layout and columns so that * can work.

Re: Making Month/Year Date From Source File Name

jonathandienst — Wed, 07 Aug 2013 11:24:37 GMT

This should do the trick, whether you are loading in a loop or using a wildcard load as below:

LOAD ....

Date(Date#(Left(FileBaseName(), 5), 'MM-YY')) As FileDate,

....

other fields

FROM *_MUV.xlsx

Hope that helps

Jonathan

Re: Making Month/Year Date From Source File Name

Wed, 07 Aug 2013 11:26:03 GMT

Thanks a million, that worked

Really appriciate it.