topic Re: Convert YearMonthkey to Year, Month in QlikView

Convert YearMonthkey to Year, Month

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Convert YearMonthkey to Year, Month

jagan — Mon, 31 Oct 2016 09:28:19 GMT

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.

Re: Convert YearMonthkey to Year, Month

its_anandrjs — Mon, 31 Oct 2016 12:01:23 GMT

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

Re: Convert YearMonthkey to Year, Month

tresB — Mon, 31 Oct 2016 12:14:04 GMT

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

Re: Convert YearMonthkey to Year, Month

Mon, 31 Oct 2016 23:33:11 GMT

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

Re: Convert YearMonthkey to Year, Month

jagan — Tue, 01 Nov 2016 02:27:00 GMT

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.