topic Re: Create Quarter from Month Field in QlikView

Create Quarter from Month Field

akpofureenughwu — Mon, 13 Mar 2017 08:05:16 GMT

Good morning everyone,

Usually I do create a quarter field from my date field that comes in this form... DD/MM/YYYY.

But I has a unique data fields that looks like this

Year	Month`	Figures
2012	January	56
2012	February	78
2012	March	90

How do I create a quarter field from the above data set?

Thank you for your anticapted response,

Regards

Re: Create Quarter from Month Field

Anonymous — Mon, 13 Mar 2017 08:08:48 GMT

just build an inline lookup table for every month with associated quarter field and then left join or applymap()

Re: Create Quarter from Month Field

tresB — Mon, 13 Mar 2017 08:25:41 GMT

First create a date field and calculate quarter like:

Load

'Q' & Ceil(Month(Date)/3) as Quarter,

Date;

Load

Date(Date#(Year&Month, 'YYYYMMMM')) as Date

From <>;

Re: Create Quarter from Month Field

akpofureenughwu — Mon, 13 Mar 2017 08:36:37 GMT

Thank Robin,

Can you throw more light on this?

Re: Create Quarter from Month Field

akpofureenughwu — Mon, 13 Mar 2017 08:39:05 GMT

It looks like the desired format but it's giving me some error messages...

Re: Create Quarter from Month Field

akpofureenughwu — Mon, 13 Mar 2017 08:44:51 GMT

Here is the Original Script

Loaded from an SQL db

LIB CONNECT TO 'Planning';

LOAD if(Len(Trim("Year")) = 0, Peek ("Year"), "Year") as "Year",

if(Len(Trim("Month")) = 0, Peek ("Month"), "Month") as "Month",

"Field",

Figures;

SQL SELECT "Year",

"Month",

"Field",

Figures

FROM " Planning".dbo.Planning;

Re: Create Quarter from Month Field

sunny_talwar — Mon, 13 Mar 2017 08:46:41 GMT

What is the exact error message you are getting?

Re: Create Quarter from Month Field

akpofureenughwu — Mon, 13 Mar 2017 08:52:35 GMT

When I introduced the script Tresco suggested...

The script becomes

Load

'Q' & Ceil(Month(Date)/3) as Quarter,

Date;

Load

Date(Date#(Year&Month, 'YYYYMMMM')) as Date;

LIB CONNECT TO ' Planning';

LOAD if(Len(Trim("Year")) = 0, Peek ("Year"), "Year") as "Year",

if(Len(Trim("Month")) = 0, Peek ("Month"), "Month") as "Month",

"Field",

Figures;

SQL SELECT "Year",

"Month",

"Field",

Figures

FROM " Planning".dbo.Planning;

After loading the script, I get this.... See attached

Regards

Re: Create Quarter from Month Field

sunny_talwar — Mon, 13 Mar 2017 08:54:38 GMT

I think you missed using * to load the existing field names... try this

LIB CONNECT TO ' Planning';

LOAD *

'Q' & Ceil(Month(Date)/3) as Quarter,

Date;

LOAD *,

Date(Date#(Year&Month, 'YYYYMMMM')) as Date;

LOAD if(Len(Trim("Year")) = 0, Peek ("Year"), "Year") as "Year",

if(Len(Trim("Month")) = 0, Peek ("Month"), "Month") as "Month",

"Field",

Figures;

SQL SELECT "Year",

"Month",

"Field",

Figures

FROM " Planning".dbo.Planning;

Re: Create Quarter from Month Field

Anonymous — Mon, 13 Mar 2017 10:44:17 GMT

I meant something like that, because you gave no further information about your month field

and so I assumed, that it is just in text format. Try to put this after your statement:

left join
LOAD * INLINE [
Month, Quarter
January, Q1
February, Q1
March, Q1
April, Q2
May, Q2
June, Q2
July, Q3
August, Q3
September, Q3
October, Q4
November, Q4
December, Q4
];

Re: Create Quarter from Month Field

akpofureenughwu — Mon, 13 Mar 2017 13:41:36 GMT

Thanks Sunny

Still having errors. See attached

Re: Create Quarter from Month Field

sunny_talwar — Mon, 13 Mar 2017 13:45:43 GMT

Missed a comma

LIB CONNECT TO ' Planning';

LOAD *,

'Q' & Ceil(Month(Date)/3) as Quarter,

Date;

LOAD *,

Date(Date#(Year&Month, 'YYYYMMMM')) as Date;

LOAD if(Len(Trim("Year")) = 0, Peek ("Year"), "Year") as "Year",

if(Len(Trim("Month")) = 0, Peek ("Month"), "Month") as "Month",

"Field",

Figures;

SQL SELECT "Year",

"Month",

"Field",

Figures

FROM " Planning".dbo.Planning;

Re: Create Quarter from Month Field

akpofureenughwu — Mon, 13 Mar 2017 16:34:46 GMT

Hello Sunny, Thank you sir,

I had to change a line so it can work...

LOAD *,

if( Isnum(Date), 'Q')& Ceil(Month(Date)/3) as Quarter;

LOAD *,

Date(Date#(Year&Month, 'YYYYMMMM')) as Date;

LOAD if(Len(Trim("Year")) = 0, Peek ("Year"), "Year") as "Year",

if(Len(Trim("Month")) = 0, Peek ("Month"), "Month") as "Month",

"Field",

Figures;

SQL SELECT "Year",

"Month",

"Field",

Figures

FROM Planning".dbo.Planning;

And it worked..

Thanks everyone

Re: Create Quarter from Month Field

akpofureenughwu — Tue, 14 Mar 2017 13:59:05 GMT

Thank you Sir