topic Re: Month in QlikView

Month

upaliwije — Wed, 18 Jun 2014 16:27:36 GMT

In my data table month is recorded in numeric form eg 1,2,3 and my script is follows

sql

select

BCOD,

YEAR,

T_MONTH,

A_MONTH

FROM UPL;

But I want to load T_Month and A_Month following format

Jan Feb Mar

Pls help me to modify the above script

Re: Month

its_anandrjs — Wed, 18 Jun 2014 16:32:46 GMT

By Map another table with the Month values

MapTable:

Mapping LOAD T_MONTH, Date#(MonthName,'MMM') as MonthName;

Load * inline

[

T_MONTH,MonthName

1,Jan

2,Feb

3,Mar

4,Apr

5,May

6,June

7,July

8,Aug

9,Sep

10,Oct

11,Nov

12,Dec

];

Source:

LOAD ApplyMap('MapTable',T_MONTH ) as T_Names,T_MONTH,A_MONTH;

LOAD * Inline

[

T_MONTH,A_MONTH

1,1

2,2

3,3

4,4

5,5

6,6

7,7

8,8

9,9

10,10

11,11

12,12

];

Re: Month

cspencer3 — Wed, 18 Jun 2014 16:34:46 GMT

In your QlikView load script you could write it as:

Load

BCOD,

YEAR,

Month(T_MONTH) as T_MONTH

Month(A_MONTH) as A_MONTH

;

sql

select

BCOD,

YEAR,

T_MONTH,

A_MONTH

FROM UPL;

This would take the SQL month and change it from a 1 to JAN, or 2 to FEB, and so on.

Re: Month

its_anandrjs — Wed, 18 Jun 2014 16:42:26 GMT

Another best option is as charles suggest direst from SQL change the MonthNames in table.

Load

BCOD,

YEAR,

T_MONTH,

A_MONTH

Month(T_MONTH) as T_MONTH_NAMES,

Month(A_MONTH) as A_MONTH_NAMES;

Sql Select

BCOD,

YEAR,

T_MONTH,

A_MONTH

FROM UPL;

Re: Month

upaliwije — Wed, 18 Jun 2014 16:52:22 GMT

Thanks both of you

Re: Month

its_anandrjs — Wed, 18 Jun 2014 16:54:39 GMT

Thanks and did you check my reply

Re: Month

upaliwije — Wed, 18 Jun 2014 17:00:14 GMT

Hi,

I check your charle reply of editing the SQL. But then It returns only Jan and Dec Months. Why is that ?

Re: Month

cspencer3 — Wed, 18 Jun 2014 17:02:49 GMT

That is really odd. I did not even notice that would happen...

I will check this out and get back to you.

Re: Month

its_anandrjs — Wed, 18 Jun 2014 17:08:24 GMT

Change your script like below script this problem is because your Month values is not in Month Date format only change bold lines and format as date and then Month.

Load

BCOD,

YEAR,

T_MONTH,

A_MONTH

Month( Date#( T_MONTH,'MM' ) ) as T_MONTH_NAMES,

Month( Date#( A_MONTH,'MM') ) as A_MONTH_NAMES;

Sql Select

BCOD,

YEAR,

T_MONTH,

A_MONTH

FROM UPL;

Re: Month

cspencer3 — Wed, 18 Jun 2014 17:09:53 GMT

It could also be done in an if statement in load script:

if(T_MONTH = 1, Jan,

if(T_MONTH = 2, Feb,

if(T_MONTH = 3, Mar,

etc...

Re: Month

upaliwije — Wed, 18 Jun 2014 17:14:22 GMT

Thanks again both of you. It is working fine