topic Re: Month field Interpretation in QlikView

Month field Interpretation

Rajesh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Month field Interpretation

felipedl — Tue, 24 Oct 2017 11:05:20 GMT

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.

Re: Month field Interpretation

prma7799 — Tue, 24 Oct 2017 11:11:28 GMT

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

Re: Month field Interpretation

Rajesh — Tue, 24 Oct 2017 11:12:50 GMT

Hi Felipe,

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

Re: Month field Interpretation

prma7799 — Tue, 24 Oct 2017 11:15:47 GMT

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

Re: Month field Interpretation

tresB — Tue, 24 Oct 2017 11:18:16 GMT

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

Load

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

Re: Month field Interpretation

felipedl — Tue, 24 Oct 2017 11:19:28 GMT

Hi Rajesh,

It's because of the apostrophe, try the following

Load * Inline

[

Month

Jan'17

Feb'17

];

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;

Re: Month field Interpretation

Rajesh — Tue, 24 Oct 2017 11:21:54 GMT

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

Thanks

Rajesh

Re: Month field Interpretation

sunny_talwar — Tue, 24 Oct 2017 11:24:09 GMT

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