Converting FilebaseName() into a Month Year Date

[jiwabhsmp]

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?

[antoniotiman]

Try this

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

or Date instead of MonthName

[jiwabhsmp]

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 · Accepted solution]

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

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

[antoniotiman]

Yes Sunny, You are right.

Regards,

Antonio

[jiwabhsmp]

Brilliant, that works.

Thanks guys!