Problem with Quater

[#1]

Hello,

Anyone has any idea why the result of the script below gives the following values for Quater

Q1
Q2
Q10
Q11
Q12

The file Web_Order.csv contains orders with date in the following format: 2010-04-06 14:44:00

Order_Head:
LOAD
Web_OrderId,
date(daystart(date#(Web_OrderDateTime, 'YYYY-MM-DD hh:mm:ss'))) as Date
FROM $(include=pathWeb.txt)Web_Order.csv (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

TempOrder:
LOAD max(Date) as MaxDate,
min(Date) as MinDate
RESIDENT Order_Head;

LET varMinDate = Num(floor(Peek('MinDate', 0, 'TempOrder')));
//LET varMinDate = Num(Peek('MinDate', 0, 'TempOrder'));
//LET varMaxDate = Num(Peek('MaxDate', 0, 'TempOrder'));
LET varMaxDate = num(today());
LET varToday = num(today());

Drop Table TempOrder;

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;

//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS Date,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate / 3)) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

[#2]

Hi Martnorm,

You can try something like this.

'Q' & ceil(Month([date]),3)/3 as [Quarter],

Regards,

Sampath Kumar.G

[#3 · Accepted solution]

Hi

check the following script...

//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS Date,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;

[#4]

Buddy,

I think you should go with Gopi's Solution

Regards,

Dushyant