Extracting month form Date filed

[#1]

Hi,

I'm using qlikview 11 and i wanted to extract Month from Date field.

I have Date field as below:

day_id
20110716
20110922
20110923

The above date is in yyyymmdd format. Now i wanted to extract the months as 07,09,09 and on the chart i wanted to show the month's name as July,September. Showing month's name can be acheived by using Load inline but how to extract the month from the date field?

[its_anandrjs]

Try like this way

Month(day_id) as Month

Or

Month( Date(Date#(day_id,'YYYYMMDD'),'MM')) as Month

Or

Month(Date(day_id,'MM')) as Month

[SunilChauhan]

Date(day_id),'MM')

or

num(mid(day_id,5,2)) as Months

[#4]

Hi Shree,

num(mid(day_id,5,2)) as Month.

You can then load a mapping table as below

LOAD * INLINE [

Month_Short, Month_Long

07, July

08, August

09, September];

And then apply the map to the new Month field to get Month_Long.

Regards,

Marius

[#5]

Hi

If you want month as 01,02,03 like this try

Date(Date#(day_id,'YYYYMMDD'),'MM')

If you want month as jul u can try like this

Month(Date#(20110716,'YYYYMMDD'))

Hope it helps

[#6]

Hi, i'm getting some syantax error here :

SQL SELECT "day_id",

    "quarter_id",

    "week_id",

    "year_id",

    "Month(day_id)" as Month

FROM HIVE.orderdetails.day1;

Not able to load this table.

[its_anandrjs]

Update now check the script

Write like

SQL SELECT "day_id",

    "quarter_id",

    "week_id",

    "year_id",

    Month("day_id") as Month,

    Month( Date(Date#("day_id",'YYYYMMDD'),'MM')) as MonthTest;

FROM HIVE.orderdetails.day1;

Note:- Your field name of date is "day_id" and you use "Month(day_id)" as Month which is wrong you have to use field name and then date or month conversion then try one of this.

SQL SELECT "day_id",

    "quarter_id",

    "week_id",

    "year_id",

    "Month(day_id)" as Month

FROM HIVE.orderdetails.day1;

[#8]

Hi,

You have to do this on a preceding load.

LOAD

"day_id",

    "quarter_id",

    "week_id",

    "year_id",

    "Month(day_id)" as Month;

SQL SELECT

"day_id",

    "quarter_id",

    "week_id",

    "year_id"

FROM HIVE.orderdetails.day1;

or

LOAD

"day_id",

    "quarter_id",

    "week_id",

    "year_id",

    "num(mid(day_id,5,2))" as Month;

SQL SELECT

"day_id",

    "quarter_id",

    "week_id",

    "year_id"

FROM HIVE.orderdetails.day1;

Regards,

Marius

[antoniotiman]

Hi,

Try

Load *,

Month(Date#(day_id,'YYYYMMDD')) as MonthName,

Mid(day_id,5,2) as MonthNumber;

SQL Select

day_id,

......

From HIVE.......;

[MK_QSL]

You can achieve Month field from your date field by writing below in your script...

Date(Date#(day_id,'YYYYMMDD'),'MMMM') as Month