topic make date in qlikview in Integration, Extension & APIs

make date in qlikview

capriconuser — Mon, 09 Aug 2021 08:33:41 GMT

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 ?

Re: make date in qlikview

Vegar — Mon, 09 Aug 2021 09:11:38 GMT

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.

Re: make date in qlikview

capriconuser — Mon, 09 Aug 2021 10:32:25 GMT

@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..

Re: make date in qlikview

Vegar — Mon, 09 Aug 2021 19:07:11 GMT

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,