Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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