Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
jiwabhsmp
Contributor II
Contributor II

Converting FilebaseName() into a Month Year Date

Hi,

I currently have .xlsx files which finish with "...UK_May 2017.xlsx", inside Qlikview, I am able to use:

Subfield(FileBaseName(),'_',-1) as FileBase

to bring in the May 2017 into a new field called Filebase, however, this is as a string. Ideally, I would like to be able to convert this to a date.

I have tried to use:

Date(Date#(Subfield(FileBaseName(),'_',-1),'DD/MM/YYYY')) as FileBase2

However, this returns no values in the new field.

Is it possible to use the Month Year file name text in the .xlsx file name to bring in the first of the month in a DD/MM/YYYY format?

1 Solution

Accepted Solutions
sunny_talwar

FileBaseName() doesn't include the file extension... this should be enough

Date(MonthStart(Date#(SubField(FileBaseName(),'_',-1), 'MMM YYYY')), 'MMM YYYY') as MonthYear

View solution in original post

5 Replies
antoniotiman
Master III
Master III

Try this

MonthName(Date#(SubField(SubField(FileBaseName(),'_',-1),'.',1),'MMM YYYY')) as FileBase

or Date instead of MonthName

jiwabhsmp
Contributor II
Contributor II
Author

Hi Antonio,

Thank you for the reply, and I've tried the above:

MonthName(Date#(SubField(SubField(FileBaseName(),'_',-1),'MMM YYYY'))) as FileBase

(I had to add another parenthesis at the end)

However, I still do not get any values returning inside my table.

sunny_talwar

FileBaseName() doesn't include the file extension... this should be enough

Date(MonthStart(Date#(SubField(FileBaseName(),'_',-1), 'MMM YYYY')), 'MMM YYYY') as MonthYear

antoniotiman
Master III
Master III

Yes Sunny, You are right.

Regards,

Antonio

jiwabhsmp
Contributor II
Contributor II
Author

Brilliant, that works.

Thanks guys!