Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

nickjose7
Contributor

How to create dates from Month-Year Strings?

kush141087serj_shumdmukramaliantoniotiman

Hi,

I need to create dates from a Month field having values like: Aug'17

Desired output: 01/08/2017

Any help would be highly appreciated

Thanks

Nick

1 Solution

Accepted Solutions
Frank_Hartmann
Honored Contributor

Re: How to create dates from Month-Year Strings?

try like that:

LOAD *, Date(Date#(left(Date,3)&'/'&right(Date,2),'MMM/YY'),'DD/MM/YYYY') as NewDate;

LOAD * INLINE [

    Date

    "Aug'17"

];

12 Replies

Re: How to create dates from Month-Year Strings?

Let me ask this date field in string date or pure date let me know.

Frank_Hartmann
Honored Contributor

Re: How to create dates from Month-Year Strings?

try like that:

LOAD *, Date(Date#(left(Date,3)&'/'&right(Date,2),'MMM/YY'),'DD/MM/YYYY') as NewDate;

LOAD * INLINE [

    Date

    "Aug'17"

];

Re: How to create dates from Month-Year Strings?

If you have any sample so share that, you have to use the Makedate function in your load script and break the Year Month from the existing Month Name string see my example and also the attached. You can do this in the single table also.

Data:

LOAD *,

Month(Date#(Left(MonthYear,3),'MMM')) as Month,

Year(Date(Date#( Right(MonthYear,2),'YY'),'YY')) as Year;

LOAD * Inline

[

MonthYear

Aug'17

];

NewData:

LOAD *,

MakeDate(Year,Month,1) as NewDate

Resident Data;

DROP Table Data;

OP7.PNG

Highlighted
antoniotiman
Honored Contributor III

Re: How to create dates from Month-Year Strings?

Hi Nick,

may be

Date(Date#(DateField,'MMM'&Chr(39)&'YY'),'DD/MM/YYYY')

Regards,

Antonio

Sergey_Shuklin
Valued Contributor

Re: How to create dates from Month-Year Strings?

Hello, Nick!

You can use MakeDate() and Left()/Right() functions to create dates from strings:

make_date.png

Expression:

=MakeDate(20&Right(mnt,2),

Pick(Match(left(mnt,3),

'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),

1,      2,       3,       4,      5,      6,      7,      8,       9,    10,    11,    12),

1)

Re: How to create dates from Month-Year Strings?

Another way also

Data:

LOAD *,

Date( Date#( PurgeChar(MonthYear,Chr(39)),'MMMYY'),'DD/MM/YYYY') as NewDates;

LOAD * Inline

[

MonthYear

Aug'17

];

Re: How to create dates from Month-Year Strings?

simplest way to do it is below

monthstart(date#(Month,'MMM''YY')) as MonthStart

nickjose7
Contributor

Re: How to create dates from Month-Year Strings?

Thank you Frank. This works perfectly.

nickjose7
Contributor

Re: How to create dates from Month-Year Strings?

Thanks Antonio. This is great.