Making dates out of text field

[melwilson]

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

[sunny_talwar]

You can try this

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

[sunny_talwar]

Or this

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

[stevedark · Accepted solution]

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

[melwilson]

Thanks for the additional information, Steve.

Mel

[melwilson]

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

[qlikviewwizard]

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];