

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
just build an inline lookup table for every month with associated quarter field and then left join or applymap()

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 <>;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank Robin,
Can you throw more light on this?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It looks like the desired format but it's giving me some error messages...


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What is the exact error message you are getting?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
];

- « Previous Replies
-
- 1
- 2
- Next Replies »