topic Converting FilebaseName() into a Month Year Date in QlikView

Converting FilebaseName() into a Month Year Date

jiwabhsmp — Wed, 25 Nov 2020 16:16:04 GMT

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 — Thu, 15 Jun 2017 15:45:41 GMT

Try this

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

or Date instead of MonthName

jiwabhsmp — Thu, 15 Jun 2017 15:57:30 GMT

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 — Thu, 15 Jun 2017 15:58:33 GMT

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

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

antoniotiman — Thu, 15 Jun 2017 16:02:08 GMT

Yes Sunny, You are right.

Regards,

Antonio

jiwabhsmp — Thu, 15 Jun 2017 16:03:54 GMT

Brilliant, that works.

Thanks guys!