topic Re: Fetch date from excel file name in QlikView

Fetch date from excel file name

prma7799 — Mon, 08 Dec 2014 04:26:11 GMT

Hi All,

I have on one excel file like "Sale (Feb-2014).xlsx" .

I want to pick above Feb-2014 from excel and link to the master calender.

Need help.

Thanks,

Prashant

Re: Fetch date from excel file name

tresB — Mon, 08 Dec 2014 04:40:34 GMT

Use filebasename(), like:

Load *,

Date(Date#(TextBetween(filebasename( ), '(',')' ), 'MMM-YYYY')) as Date

From <>;

Re: Fetch date from excel file name

prma7799 — Mon, 08 Dec 2014 04:52:52 GMT

Hi Tresesco,

It is working you have any master calender script so I link them to each other.

Thanks,

Re: Fetch date from excel file name

Anonymous — Mon, 08 Dec 2014 04:53:10 GMT

You could use the function "FileName" in your script. This will give you the whole filename.

To pick the actual date part out you can use the "Mid" function, you could also use "Subfield" as well but in this case that would require more steps.

This is an example of how it might look.

Mid(FileName(),6,8) As FileNameDate,

Re: Fetch date from excel file name

tresB — Mon, 08 Dec 2014 04:56:51 GMT

Check this: Creating A Master Calendar

Re: Fetch date from excel file name

Anonymous — Mon, 08 Dec 2014 04:59:17 GMT

Trescos solution is of course much better, a bit early in the morning for me (05:00 local time)