Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table with following columns.
Month Number | Month Year | YEAR |
1 | January | 1990 |
5 | January | 1990 |
1 | February | 1990 |
4 | February | 1990 |
I need to create a DATE OBJECT (CALENDAR OBJECT) with these columns. Can anyone help me with this ?
hi ,
First create master calender then use Date_Field as input in calendar object.
How to create it ?
Which column, I should use for creating it...
Can Iuse this code for Master Calendar ?
MasterCalendar:
Load
TempDate As OrderDate,
Week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month
resident TempCalendar;
Hi Praveen,
Use below code for master calender :
//Script One
Let varMinDate = num(MakeDate(2000,1,1));
Let varMaxDate = num(MakeDate(2020,12,31));
//*************** 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 CalDate,
If(weekday(TempDate)='Sat' or weekday(TempDate)='Sun',0,1) as PlannedBD2,
1 As PlannedBD,
weekday(TempDate) as CalDayStr,
Week(TempDate) AS CalWeek,
WeekYear(TempDate) AS CalTestWeek,
Year(TempDate) AS CalYear,
Month(TempDate) AS CalMonth,
year(TempDate)&'-'&Month(TempDate) AS CalYear_Month,
MonthName(TempDate) AS CalYearMonth,
Day(TempDate) AS CalDayNo,
'Q'&ceil(month(TempDate)/3) AS CalQuarter
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
If you want to create multiple calender objects then you should create data Island (separate table for each field for calendar object)
Pro_Start_Date:
LOAD TempDate AS _QA_Start_Date
RESIDENT TempCalendar ;
UAT_Start_date:
LOAD TempDate AS _UAT_Start_date
RESIDENT TempCalendar ;
Thanks Balkumar.
And should I add the varMinDate and varMaxDate in SHEET PROPERTIES ?
I meant, Should I declare the values in Sheet properties like varMinDate = min(Month_Year) and varMaxDate= max(Month_Year)
As your script loaded first so you need to assign varMinDate and varMaxDate in script itself. If you want to use min date and max date in your UI you can reassign the values to both variables in sheet as well for UI use.
Actually, My doubt is, Like where am I declaring the Column name in the code ?
As per my table, MONTH_YEAR is the column, which has the Date information as "JAN 2011",... etc.
So Should I declare Month_Year as the column in place of Cal_Year ?
Yes replace the columns as required.