Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
PLS SHARE SCRIPT FOR MASTER CALENDAR HAVING A FIELD POSTING DATE?
Hello,
Please use the script below to create a master calendar for your date column.
please update DATECOLUMN and SOURCETABLE in the script .
You might have to re-do the same for other date columns.
*****************************************
// Please update your DATECOLUMN (3 places) and Source table name (SOURCETABLE)
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(DATECOLUMN) as minDate,
max(DATECOLUMN) as maxDate
Resident SOURCETABLE
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS DATECOLUMN,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
******************************************************
Hope it helps!!
Thanks
Hi Manoj,
Script for Master Calender:
Let StartCalendar = num(MakeDate(year(today())-1, '01', '01'));
//Let EndCalendar = num(YearEnd(today()));
//---------------------------------------------------------------------
//Autogenerate Calendar with start and end Date
Cal:
LOAD Date ($(StartCalendar) + RecNo()-1) as CalDate
AutoGenerate ((today()-1) - $(StartCalendar)+1 );
//Left Join with no keys
left Join (Cal)
Load date(CalDate, 'DD-MMM-YYYY') as DateID
Resident Cal;
//CAL:
//Load
// DateID,
//// Date(SALES_DATE, 'DD/MM/YYYY') as SALES_DATE_1,
// Month(DateID) as SALES_MONTH,
// Year(DateID) as SALES_YEAR,
// MonthName(DateID) as SALES_MONTHNAME,
// 'Q' & Ceil(Month(DateID)/3) AS SALES_QUARTER,
// DayNumberOfYear(DateID) as DayNumberOfYear,
// Day(DateID) as DayNumberOfMonth,
// DayNumberOfQuarter(DateID) as DayNumberOfQuarter,
// num(Month(DateID)) as MonthNum
// Resident Cal;
//drop Table Cal;
You can modify this script according to your Date filed.
Thanks,
AS
my field name is Posting coloumn and table is LFA1 . pls tell me the exact place where i need to replace the these keywords. pls i am new to scripting and qlikview also
Try this master calendar
//Calendar:
LET vDateMin = Num(MakeDate(2011,01,01));
Let vDateMax = Floor(Today());
TempCalendar:
LOAD
date($(vDateMin)+IterNo()-1) AS Datefield,
date($(vDateMin)+IterNo()-1) AS TempDate
AUTOGENERATE (1)
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar:
LOAD
TempDate as PostingDate, //conect this column with your table field with table LFA1
Date(TempDate) AS ActionTime,
// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,
WeekDay(TempDate) AS CalendarDayName,
Week(TempDate) AS CalendarWeekOfYear,
Month(TempDate) AS CalendarMonthName,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQtr,
Year(TempDate) AS CalendarYr,
// Calendar Date Names
WeekName(TempDate) as CalendarWeekNumberAndYear,
MonthName(TempDate) as CalendarMonthAndYear,
QuarterName(TempDate) as CalendarQuarterMonthsAndYear,
// Start Dates
DayStart(TempDate) as CalendarDayStart,
WeekStart(TempDate) as CalendarWeekStart,
MonthStart(TempDate) as CalendarMonthStart,
QuarterStart(TempDate) as CalendarQuarterStart,
YearStart(TempDate) as CalendarYearStart,
// End Dates
DayEnd(TempDate) as CalendarDayEnd,
WeekEnd(TempDate) as CalendarWeekEnd,
MonthEnd(TempDate) as CalendarMonthEnd,
QuarterEnd(TempDate) as CalendarQuarterEnd,
YearEnd(TempDate) as CalendarYearEnd,
// Combo Date Examples
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,
//'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays,
//Relative Dates
Today() - TempDate as DaysAgo,
(WeekStart(Today()) - WeekStart(TempDate))/7 as WeeksAgo,
12*(Year(Today())-Year(TempDate)) + Month(Today()) - Month(TempDate) as MonthsAgo,
MonthName(today()) as ThisMonth,
MonthName(AddMonths(today(),-1)) as PrevMonth
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
Hi,
tmpCalender:
LOAD
min(Date) as MinDate,
max(Date) as MaxDate
Resident tmpDate;
Quarter_Map:
Mapping
LOAD * INLINE [
Month, Quarter
Jan, Q2
Feb, Q2
Mar, Q2
Apr, Q3
May, Q3
Jun, Q3
Jul, Q4
Aug, Q4
Sep, Q4
Oct, Q1
Nov, Q1
Dec, Q1
];
/////////////////////////////////////
LET vDateMin = Num(Peek('MinDate', 0, 'tmpCalender'));
LET vDateMax = Num(Peek('MaxDate', 0, 'tmpCalender'));
LET vDateToday = Num(Today());
TempCalendar1:
LOAD
$(vDateMin) + RowNo() - 1 AS Date_Key,
Date($(vDateMin) + RowNo() - 1) AS Date
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Master_Calendar:
load Date_Key
,Date as Date
,Date as Receipt.ReceiptDate
,Date as CalendarDate
,Day(Date) as CalendarDay
,Week(Date) as CalendarWeek
,WeekName(Date) as CalendarWeekName
,month(Date) as CalendarMonth
,MonthName(Date) as CalendarMonthName
,MonthName(Date) as CalendarPeriod
,Quartername(Date) as CalendarQuarterName
,ApplyMap('Quarter_Map',num#(month(Date)))as CalendarQuarter
,year(Date) as CalendarYear
,WeekDay(Date) as CalendarWeekDay
,text(weekday(Date)) as Week_Day1
,InYearToDate(Date,$(vDateToday),0)* -1 as CurYTDFlag
,inyeartodate(Date, $(vDateToday), -1) * -1 as LastYTDFlag
Resident TempCalendar1;
DROP Table TempCalendar1;
DROP Table tmpCalender;
DROP Table tmpDate;
Hope this can help you.
thanks,
Raja.
i am also from meeru ekkada undedhi brother
Copy the script to a Notepad -> CTRL+H ->
Find for ‘DATECOLUMN’ Replace with ‘Posting’
Find for ‘SOURCETABLE’ Replace with ‘LFA1’