Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
melwilson
Contributor II
Contributor II

Making dates out of text field

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

1 Solution

Accepted Solutions
stevedark
MVP
MVP

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

View solution in original post

6 Replies
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
MVP
MVP

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
Contributor II
Contributor II
Author

Thanks for the additional information, Steve.

Mel

melwilson
Contributor II
Contributor II
Author

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
Master II
Master II

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

Capture.PNG