Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I want to generate a calendar with my date (Min & Max).
But When I reload I see more than 12 millions of rows to generate a calendar from 25/02/2014 13:30:01 to 25/05/2014 12:11:34.
Can someone help me, please ?
Thanks.
Hi,
Check this thread hope it helps you.
Master Calendar Generation Script
Try this script below
CalendarMaster:
LOAD
Date(TempDate) AS TempDate,
Year(TempDate) AS Year,
'Q' & Ceil(Month(TempDate) / 3) AS Quarter,
Month(TempDate) As Month,
Day(TempDate) As Day,
Week(TempDate) As Week;
Load Date(MinDate + IterNo() -1 ) AS TempDate While (MinDate + IterNo() - 1) <= Num(MaxDate);
LOAD
Floor(min(date_create)) AS MinDate,
Floor(max(date_create)) AS MaxDate
RESIDENT Temp_Time;
Regards,
jagan.
Looks weird. Just commented the table in your loading script and added "25/02/2014 13:30:01" as Min Date, "25/05/2014 12:11:34" as Max Date, variables quoted with single inverted comma in loading script and it is giving me 89 rows. See below script,
//TEMP:
//LOAD
// num(min(date_create)) AS MinDate,
// num(max(date_create)) AS MaxDate
//RESIDENT Temp_Time;
LET vMinDate = Date#('25/02/2014 13:30:01', 'DD/MM/YYYY hh:mm:ss');//peek('MinDate', 0, 'TEMP');
LET vMaxDate = Date#('25/05/2014 12:11:34', 'DD/MM/YYYY hh:mm:ss');//peek('MaxDate', 0, 'TEMP');
//DROP TABLE Temp_Time;
TempCalendar:
LOAD
date('$(vMinDate)'+IterNo()-1) AS TempDate
AUTOGENERATE (1)
WHILE '$(vMinDate)'+IterNo()-1<= '$(vMaxDate)';
Hi,
Check this thread hope it helps you.
Master Calendar Generation Script
Try this script below
CalendarMaster:
LOAD
Date(TempDate) AS TempDate,
Year(TempDate) AS Year,
'Q' & Ceil(Month(TempDate) / 3) AS Quarter,
Month(TempDate) As Month,
Day(TempDate) As Day,
Week(TempDate) As Week;
Load Date(MinDate + IterNo() -1 ) AS TempDate While (MinDate + IterNo() - 1) <= Num(MaxDate);
LOAD
Floor(min(date_create)) AS MinDate,
Floor(max(date_create)) AS MaxDate
RESIDENT Temp_Time;
Regards,
jagan.
hi,
try this
LET varMinDate = Num(MakeDate(2008,8,31));
LET varMaxDate = Floor(MonthEnd(Today()));
LET varToday = Date(Today(), 'MM/DD/YYYY');
LET varYesterday = Date(Today()-1, 'MM/DD/YYYY');
LET varCurrentMonth = Date(Monthstart(Today()-1), 'MMM-YYYY');
//************************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<= num(today());
//**************************Master Calendar******************************
//Building the master calendar with most date dimensions
MasterCalendar:
LOAD
TempDate as InvDate,
Day(TempDate) as Day,
If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Num(Month(TempDate))+9,Num(Month(TempDate))-3) as CMonth,
If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Year(TempDate)-1,Year(TempDate))as CYear,
Day(TempDate)&'/'&If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Num(Month(TempDate))+9,Num(Month(TempDate))-3)&'/'&If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Year(TempDate)-1,Year(TempDate)) as InvFinDate,
YearName(TempDate, 0, 4) as FinYearName,
Weekday(TempDate) as WeekDay,
Week(TempDate) as Week,
Month(TempDate) as Month,
Year(TempDate) as Year,
MonthName(TempDate) AS MonthYear,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
'Q' & ceil((If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Num(Month(TempDate))+9,Num(Month(TempDate))-3)) / 3) as FinQuarter,
'Q' & ceil((If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Num(Month(TempDate))+9,Num(Month(TempDate))-3)) / 3) & ' '&If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Year(TempDate)-1,Year(TempDate)) AS QuarterYear,
date(Today()-1) as Yesterday,
inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag
resident TempCalendar
order by TempDate Asc;
Thank you,so much