Month

[upaliwije]

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

[its_anandrjs]

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

];

[cspencer3 · Accepted solution]

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.

[its_anandrjs]

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;

[upaliwije]

Thanks both of you

[its_anandrjs]

Thanks and did you check my reply

[upaliwije]

Hi,

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

[cspencer3]

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

I will check this out and get back to you.

[its_anandrjs]

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;

[cspencer3]

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...