Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted

You can try this

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

Highlighted

Or this

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Contributor II
Contributor II

Thanks for the additional information, Steve.

Mel

Highlighted
Contributor II
Contributor II

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

Highlighted
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