topic Re: Making dates out of text field in QlikView

Making dates out of text field

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

Hello.

I am really new to this. I have a text field (called Period) containing date information. I want to create a Month, Year and a YearMonth field but don't know how to do it. Can anyone help, please ?

The contents of this Period field looks like 2017-M03, 2017-M04 etc

I have seen a function Date# but don't know how to strip out the M.

Thanks,

Mel

Re: Making dates out of text field

sunny_talwar — Fri, 08 Jun 2018 15:28:28 GMT

You can try this

Date(Date#(PurgeChar(Period, 'M'), 'YYYY-MM'), 'YYYY-MM') as YearMonth

Re: Making dates out of text field

sunny_talwar — Fri, 08 Jun 2018 15:40:25 GMT

Or this

Date(Date#(Replace(Period, 'M', ''), 'YYYY-MM'), 'YYYY-MM') as YearMonth

Re: Making dates out of text field

stevedark — Fri, 08 Jun 2018 17:06:16 GMT

Mel,

Once you have the field that Sunny has described how to create you can do other stuff with it in a preceding load, you can also change the format of it;

For example;

LOAD

Month(YearMonth) as Month,

Year(YearMonth) as Year

;

LOAD

Period,

Date(Date#(Replace(Period, 'M', ''), 'YYYY-MM'), 'MMM-YY') as YearMonth,

etc.

This will give you four fields from your original field, as follows:

Period: 2017-M03

YearMonth: Mar 2017

Month: Mar

Year: 2017

Hope that helps.

Steve

Re: Making dates out of text field

melwilson — Mon, 11 Jun 2018 08:14:24 GMT

Thanks for the additional information, Steve.

Mel

Re: Making dates out of text field

melwilson — Mon, 11 Jun 2018 08:15:41 GMT

Thanks Sunny. I was trying to go through the list of commands in the Help option to work out what to use but I got a bit lost.

Mel

Re: Making dates out of text field

qlikviewwizard — Mon, 11 Jun 2018 08:35:43 GMT

Hi,

Try Like this.

Data:

LOAD *,Month(MonthYear) as Month,Year(MonthYear) as Year;

LOAD *,DATE#(left(Period,4)&'-'&SubField(Period,'-M',2),'YYYY-MM') as MonthYear;

LOAD * INLINE [

Period

2017-M01

2017-M02

2017-M03

2017-M04

2017-M05

2017-M06

2017-M07

2017-M08

2017-M09

2017-M10

2017-M11

2017-M12];