Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Making Month/Year Date From Source File Name

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

Tags (3)
1 Solution

Accepted Solutions
jonbrough
Valued Contributor

Re: Making Month/Year Date From Source File Name

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

NEXT

Jonathan

5 Replies
jonbrough
Valued Contributor

Re: Making Month/Year Date From Source File Name

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

NEXT

Jonathan

richard_pearce6
Valued Contributor

Re: Making Month/Year Date From Source File Name

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.

Not applicable

Re: Making Month/Year Date From Source File Name

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.

MVP
MVP

Re: Making Month/Year Date From Source File Name

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Making Month/Year Date From Source File Name

Thanks a million, that worked

Really appriciate it.

Community Browser