topic Re: Extracting month form Date filed in QlikView

Extracting month form Date filed

Wed, 25 Jun 2014 07:12:11 GMT

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?

Re: Extracting month form Date filed

its_anandrjs — Wed, 25 Jun 2014 07:15:41 GMT

Try like this way

Month(day_id) as Month

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

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

Re: Extracting month form Date filed

SunilChauhan — Wed, 25 Jun 2014 07:18:47 GMT

Date(day_id),'MM')

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

Re: Extracting month form Date filed

Wed, 25 Jun 2014 07:23:26 GMT

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

Re: Extracting month form Date filed

Wed, 25 Jun 2014 07:26:03 GMT

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

Re: Extracting month form Date filed

Wed, 25 Jun 2014 07:26:50 GMT

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.

Re: Extracting month form Date filed

its_anandrjs — Wed, 25 Jun 2014 07:29:31 GMT

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;

Re: Extracting month form Date filed

Wed, 25 Jun 2014 07:30:29 GMT

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;

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

Re: Extracting month form Date filed

antoniotiman — Wed, 25 Jun 2014 07:40:16 GMT

Hi,

Try

Load *,

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

Mid(day_id,5,2) as MonthNumber;

SQL Select

day_id,

......

From HIVE.......;

Re: Extracting month form Date filed

MK_QSL — Wed, 25 Jun 2014 08:13:17 GMT

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

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

Re: Extracting month form Date filed

Wed, 25 Jun 2014 08:28:33 GMT

Hi, the solution suggested by you is working fine for me. Here i have small doubt. Why we are loading first and how it will come to know that we are loading date table as we are not mentioning any hive database or table name in the load statement.

Please clarify this doubt.

Re: Extracting month form Date filed

antoniotiman — Wed, 25 Jun 2014 08:37:36 GMT

Hi,

this is a behavior of QV called PRELOAD.

The PRELOAD works in Select/Load following.

Is the same that Select/Load and following Load ... Resident.

Regards,

Antonio

Re: Extracting month form Date filed

SunilChauhan — Wed, 25 Jun 2014 08:40:51 GMT

we can load a many as load statement above a load with path.

Re: Extracting month form Date filed

Wed, 25 Jun 2014 09:34:00 GMT

its not working

Re: Extracting month form Date filed

its_anandrjs — Wed, 25 Jun 2014 10:11:10 GMT

Try your script with preceding load statement then load like below scripts

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;

Re: Extracting month form Date filed

Wed, 25 Jun 2014 10:16:44 GMT

my date field is in the format of 2022011

2 date 02 month 2011 year

how to retrieve that month?

'in the excel file date is

20022011

bcoz 0 is the valueless before number

Re: Extracting month form Date filed

Anonymous — Wed, 25 Jun 2014 10:19:50 GMT

Hi,

Something like below,

Month(Date#(02022011, 'DDMMYYYY'))

Re: Extracting month form Date filed

its_anandrjs — Wed, 25 Jun 2014 10:53:47 GMT

If in excel it is in format 20022011 means DDMMYYYY

Then use

Month(Date#(Datefield,'DDMMYYYY'))

Month(Date(Date#(Datefield,'DDMMYYYY'),'MM'))

If in excel it is in format 2022011 means DMMYYYY

Month(Date#(Datefield,'DMMYYYY'))

Month(Date(Date#(Datefield,'DMMYYYY'),'MM'))