topic Re: Creating a variable from a file name in QlikView

Creating a variable from a file name

Anonymous — Fri, 29 Sep 2017 17:26:19 GMT

How would I create a variable from a file name?

For example,

I have an excel file with the name TimeTrackingReport_08.20.2017.xlsx

I want to create the variable vDate to pull the date depending on the file name

Re: Creating a variable from a file name

its_anandrjs — Fri, 29 Sep 2017 17:33:39 GMT

I believe you have single file then use the FileBaseName(), FileName(0 for this and store in variable.

Re: Creating a variable from a file name

its_anandrjs — Fri, 29 Sep 2017 17:50:17 GMT

Load your table this way with FileBaseName() you can use FileName() also

Ex:-

Tab1:

LOAD Right(FilebaseName(),10) as Date

FROM

[..\TimeTrackingReport_08.20.2017.xlsx]

(ooxml, no labels, table is Sheet1);

LET vDate = Peek('Date',0,'Tab1');

Re: Creating a variable from a file name

Anonymous — Fri, 29 Sep 2017 19:18:54 GMT

That works great, thanks!

Now I just need to format the date as mm/dd/yyyy

(I changed the file names to say 08202017 instead of 08.20.2017)

I tried the following :

Date(DATE#(Right(FileName(),10,'MMDDYYYY'),'MM/DD/YYYY') AS DATE

and got

Error in expression:

Right takes 2 parameters

Re: Creating a variable from a file name

Frank_Hartmann — Fri, 29 Sep 2017 19:30:45 GMT

Date(DATE#(Right(FileName(),10),'MMDDYYYY'),'MM/DD/YYYY') AS DATE

Re: Creating a variable from a file name

Anonymous — Fri, 29 Sep 2017 19:41:53 GMT

Missed that, thanks!

I didn't get an error, but the output is incorrect. It gives me "-"

Re: Creating a variable from a file name

its_anandrjs — Fri, 29 Sep 2017 19:44:03 GMT

Here it is

Date(Date#( Right(FilebaseName(),10),'MM.DD.YYYY'),'MM/DD/YYYY') as Date

Your complete code is

Tab1:

LOAD

Date(Date#( Right(FilebaseName(),10),'MM.DD.YYYY'),'MM/DD/YYYY') as Date

FROM

[..\TimeTrackingReport_08.20.2017.xlsx]

(ooxml, no labels, table is Sheet1);

LET vDate = Peek('Date',0,'Tab1');

Re: Creating a variable from a file name

its_anandrjs — Fri, 29 Sep 2017 19:46:26 GMT

I suppose this worked for you check this

If you remove dot then number changes

Date(Date#( Right(FilebaseName(),8),'MMDDYYYY'),'MM/DD/YYYY') as Date

Re: Creating a variable from a file name

Frank_Hartmann — Fri, 29 Sep 2017 19:49:04 GMT

Date(DATE#(Right(FilebaseName(),8),'MMDDYYYY'),'MM/DD/YYYY') AS DATE