Capturing a date that's embedded in an Excel filename

[nickking]

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

[Gysbert_Wassenaar]

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

[lukaspuschner · Accepted solution]

HI,

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

[nickking]

Thanks Lukas. That worked.