make date in qlikview

[capriconuser]

I extract month and year from file name 

Filename is 

File-June-2021.xlsx

And i finally extract MOnth and year from this file in script 

now i want to extract date from filename .. like this

01-06-2021

like every date should start from 1 

how i do this ?

[Vegar · Accepted solution]

You need to figure a way to extract the 'Month-Year' from your file. When you 've done that you can use Date#('June-2021', 'MMMM-YYYY') to intrepret the string as a date. There after you can format the date as you please using Date(). It will be something like this.

Date(Date#('June-2021', 'MMMM-YYYY'), 'DD-MM-YYYY')

Take a look at my script image below.

[capriconuser]

@Vegar hi i already month and year from file name like this 

load

--

Trim(Replace(FileBaseName(),'MMailBoxUsage-','')) as MailBox_Date

from 

abc-June-2021.xlsx

and i extract month and year like this 

Year(Date#(MailBox_Date,'MMMM-YYYY')) as MailBox_Year,
Month(Date#(MailBox_Date,'MMMM-YYYY')) as MailBox_Month,

now i want to extract date.. 

[Vegar · Accepted solution]

If you have the month and the year value then you can create a date using makedate().

Makedate(MailBox_Year,MailBox_Month,1) as MailBox_Date,

Or use monthstart() on your date#() expression like this. 

Monthstart(Date#(MailBox_Date,'MMMM-YYYY')) as MailBox_Date,