Convert YearMonthkey to Year, Month

[#1]

Hello,

I have a YearMonthkey like,  '201607', in varchar format, I would like to convert YearMonthkey to Year, and month accordingly.

I am using the following script:

Map_Month:

Mapping LOAD * INLINE

[

MonthNum, MonthName

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, Jun

7, Jul

8, Aug

9, Sep

10, Oct

11, Nov

12, Dec

];

LOAD

          YearMonthKey,

          left(YearMonthKey, 4)          as Year,

          num(right(YearMonthKey, 2))           as MonthNum,

          ApplyMap('Map_Month', num(right(YearMonthKey, 2)), '-')          as MonthName,

FROM......

Should you have another solution, Please kind help, thank you.

[jagan]

Hi,

Try like this

Data:

LOAD

*,

Year(Date) AS Year,

Month(Date) * 1 AS MonthNum,

Month(Date) AS MonthName;

LOAD

          YearMonthKey,

          Date(Date#(MonthName, 'YYYYMM')) AS Date

FROM......

Hope this helps you.

Regards,

Jagan.

[its_anandrjs]

If you have any date field then try like this

LOAD

Year(DateField) as Year,

Month(DateField) as Month

NUM(Month(DateField)) as NUMMonth

FROM

Other wise your sol is correct also and Jagan suggest is also correct.

Regards,

Anand

[tresesco · Accepted solution]

I would do it like:

LOAD

          YearMonthKey,

          left(YearMonthKey, 4)          as Year,

          num(right(YearMonthKey, 2))           as MonthNum

         Month(MakeDate(left(YearMonthKey, 4), num(right(YearMonthKey, 2))))  as Month

FROM......

[#5]

There are more than one correct answers, Thank you all for all reply!!

[jagan]

Hi Lee,

It is always a good practice to have a date field in data model in addition to Year and Month to implement expressions like last 12 mnths, 6 months, 5 years etc.

Regards,

Jagan.