Create Quarter from Month Field

[akpofureenughwu]

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

[Anonymous]

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

[tresesco]

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

[akpofureenughwu]

Thank Robin,

Can you throw more light on this?

[akpofureenughwu]

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

[akpofureenughwu]

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;

[sunny_talwar]

What is the exact error message you are getting?

[akpofureenughwu]

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

[sunny_talwar]

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;

[Anonymous]

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
];