Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
akpofureenughwu
Creator III

Create Quarter from Month Field

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

YearMonth`Figures
2012January56
2012February78
2012March90

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

Thank you for your anticapted response,

Regards

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

13 Replies
Anonymous
Not applicable

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

tresesco
MVP

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
Creator III
Author

Thank Robin,

Can you throw more light on this?

akpofureenughwu
Creator III
Author

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

akpofureenughwu
Creator III
Author

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
Creator III
Author

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
Not applicable

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