Month field Interpretation

[Rajesh]

Hi All,

I am trying to do Interpretation on Month field(Source is Excel) using Date,Date# function

the field values like Jan'17,Feb'17...

and then after would like to do the sort as calendar month.

Can any one suggest.

Thanks

Rajesh

[felipedl]

Hi Rajesh,

Try the following code on the load script:

Load

     Date#(Month,'MMM'YY') as Month // this is formatting the date that Qlik can't automatticaly interpret into a date,

From [Whatever];

Felipe.

[prma7799]

Try like this

Edited with double quotes

Load

    Month(Date#('Jan'17,','MMM''YY') as Month  ,

    Num(Month(Date#(Month,'MMM''YY'))) as MonthNumber

From .....;

after that user MonthNumber for sorting

[Rajesh]

Hi Felipe,

Thanks for u r reply, but its giving syntax error in script.

[prma7799]

Can you please share some sample app or share error screen shot

[tresesco]

Use two single quotes (one is escape sequence) like:

Load

    MonthName(Date#(Month,'MMM''YY')) as MonthNames

[felipedl]

Hi Rajesh,

It's because of the apostrophe, try the following

x:

Load * Inline

[

Month

Jan'17

Feb'17

];

y:

Load

Date#(Month,'MMM'&chr(39)&'YY') as Month,

// Can be done by the bellow formula as well

//Date#(replace(Month,chr(39),'-'),'MMM-YY') as Month,

Month as T

Resident x;

[Rajesh · Accepted solution]

Hi

Got it now a small change in like Date(Date#(Month,'MMM''YY'),'MMM-YY') as Month,

Thanks

Rajesh

[sunny_talwar]

This worked for me

Table:

LOAD Date(Date#(MonthYear, 'MMM''YY')) as Date,

MonthYear;

LOAD * Inline [

MonthYear

Jan'17

Feb'17

];

Read about Escape Sequence here:

Escape sequences