Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

nickking
New Contributor II

Capturing a date that's embedded in an Excel filename

Hi all,

I am loading a weekly data file into my Qlikview model. The filename finishes with the date of the file. I have a number of measures that need to run off that date. At present I am using an input formula to get the user to manually enter the date each time they reload but I'm wondering if there is a way to extract the date from the filename?

The filename is in the following format - Ireland IHD export -151214.xlsx. I know how to reformat the date into a normal date format but don't know how to capture it from the filename?

Thanks very much for your help

Nick

1 Solution

Accepted Solutions
lukaspuschner
Contributor

Re: Capturing a date that's embedded in an Excel filename

HI,

use left(right(Filename(),10),6) as Date

3 Replies

Re: Capturing a date that's embedded in an Excel filename

Use the filename() function in the load statement to retrieve the filename. You can then extract the date from the file name:

LOAD *,

     date#( textbetween(FileName(),'-','.') ,'DDMMYY') as FileDate

FROM MyExcelFile-151214.xlsx

(ooxml, embedded labels, table is Sheet1);


talk is cheap, supply exceeds demand
lukaspuschner
Contributor

Re: Capturing a date that's embedded in an Excel filename

HI,

use left(right(Filename(),10),6) as Date

nickking
New Contributor II

Re: Capturing a date that's embedded in an Excel filename

Thanks Lukas. That worked.

Community Browser