Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How can i use Quater funtion if i have mnths in one column and year in other column.
Hi,
You can add your quarter in a load script and use as other columns.
Write this in your load script.
Load
Date,
'Q' & Ceil(Month(Date)/3) As Quarterly
from tableName
Regards,
Ashutosh
Try this
'Q' & Ceil(CalendarMonthNameField/3) as Quarter
hi
try this
//************************* Example one*******************************
SET STARTDATE = ’01-01-2006′; //fill in your start date
LET ENDDATE = date(today());
[TEMP_DATE]:
LOAD
date( date#(‘$(STARTDATE)’,'DD-MM-YYYY’)-1 + recno() ,’DD-MM-YYYY’) as DATE
AUTOGENERATE (date#(‘$(ENDDATE)’) – date#(‘$(STARTDATE)’))+1;
[DATETABLE]:
LOAD
DATE,
Year(DATE) as YEAR,
Month(DATE) as MONTH,
Week(DATE) as WEEK,
WeekDay(DATE) as WEEKDAY,
Day(DATE) as DAY,
Year(DATE) & right(’00′ & week(DATE),2) as YYYYWW,
Year(DATE) & right(’00′ & num(Month(DATE)),2) as YYYYMM,
Year(DATE) & ceil(num(Month(DATE))/3) as YYYYK,
‘Quarter’ & ceil(num(Month(DATE))/3) as QUARTER
RESIDENT [TEMP_DATE];
DROP TABLE [TEMP_DATE];
//************************* Example two*******************************
We need the highest and lowest date values from the fact table and place these values into variables that we use to create a Calender table. Search for the date field (in this example we will call the field FactDate) in the fact table. FactDate will be the connecting field to the Calendar table. Sort the fact table by typing the following at the end of the select statement before the semicolon:
* ORDER BY FactDate ASC;
* Your fact table should look something like this:
//************************* Fact table*********************************
Facts:
LOAD FactID,
FactName,
FactFreight,
FactShipperID,
FactDate
FROM facts ORDER BY FactDate ASC;
(Remember that you should only have one date / time field in the fact table, the master calendar table will contain all the date formats.)
* Create a new tab in the script editor and call it Calendar.
* Create a new variable for the first date by typing the following script statement:
LET varMinDate = Num(Peek(‘FactDate’, 0, ‘Facts’));
Where FactDate is the linking Date field, 0 is the first row of the table and Facts is the FactTable. When there is more then one fact table, for example Facts2, your statement should look like this:
LET varMinDate = Num(rangemin(Peek(‘FactDate’, 0,’Facts’), Peek(‘FactDate’, 0,’Facts2′)));
* Create a second variable for the last date by typing the following statement:
LET varMaxDate = Num(Peek(‘FactDate’, -1, ‘Facts’));
* Create a third variable for the last date by typing the following statement:
LET vToday = num(today());
* Create a new table by typing the following script into the script editor:
//************************Temporary Calendar***************************
//Create all days in the range from varMinDate to varMaxDate
TempCalendar:
LOAD
$(varMinDate)+Iterno()-1 AS Num,
Date($(varMinDate)+Iterno()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= $(varMaxDate);
* Once we have created all the dates needed, we can start creating a Master Calendar table where we create all the date fields needed.
//**************************Master Calendar******************************
//Building the master calendar with most date dimensions
MasterCalendar:
load TempDate as FactDate,
week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
Year2date(TempDate)*-1 as CurYTDFlag,
Year2date(TempDate,-1)*-1 as LastYTDFlag,
Date(monthstart(TempDate), ‘MMM-YYYY’) AS MonthYear,
Week(TempDate)&’-'&Year(TempDate) as WeekYear,
Weekday(TempDate) as WeekDay
resident TempCalendar order by TempDate Asc;
* Before we reload the script, we need to get rid of the TempCalendar table used for creating the dates:
Drop Table TempCalendar;
Save and reload your script
Hi Agnivesh,
Please try this
Load MONTH,
'Q' & Ceil(Num(MONTH)/3) As Quarter
from TableName;
hope it will work for u.
Thanks
Vishal Waghole
Hi Agnivesh,
1. You can create the Quarter using Inline also
LOAD
* INLINE
[
Month, Quarter
Jan, Q1
Feb, Q1
Mar, Q1
Apr, Q2
May, Q2
Jun, Q2
Jul, Q3
Aug, Q3
Sep, Q3
Oct, Q4
Nov, Q4
Dec, Q4
]
;
By this way you can create the Quarter for Fiscal Calendar also.
2. You can create in the script using "if" condition.
Hope this helps!
Please get back in case more help required.
--
Vinoth
what is the meaning of Ceil
Hi agnivesh,
If you value is 3.2 then Ceil function will return 4
and
Floor function will return 3.
Thanks and Regards,
Vishal Waghole