Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
karlykrebs
Contributor III
Contributor III

Creating a variable from a file name

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

1 Solution

Accepted Solutions
its_anandrjs

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

View solution in original post

8 Replies
its_anandrjs

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

its_anandrjs

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

karlykrebs
Contributor III
Contributor III
Author

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

Frank_Hartmann
Master II
Master II

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

karlykrebs
Contributor III
Contributor III
Author

Missed that, thanks!

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

its_anandrjs

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

its_anandrjs

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

View solution in original post

Frank_Hartmann
Master II
Master II

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