Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
Can Some One please help me with the following errors
Here is my Script for Master Calendar
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min (date([AE Start Date])) as minDate,
max (date([AE Start Date])) as maxDate
Resident AE;
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
date(TempDate) AS [AE Start Date],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
num( Month(TempDate)) as MonthNo,
Month(TempDate)*100+Day(TempDate) as DayOfYear,
// Month(TempDate)&'-'& Year(TempDate) as MonthYear,
Date(MonthStart(TempDate),'MMM-YYYY') as MonthYear,
Year(TempDate) &Num(Month(TempDate)) as YearMonth,
//if(Year(TempDate)=WeekYear(TempDate), Week(TempDate)-Week(MonthStart(TempDate)) +1, Week(TempDate)+52 -Week(MonthStart(TempDate)) +1) as WeekinMonth,
//'Week' & Ceil(Day(TempDate)/7) as WeekInMonth,
Ceil(Day(TempDate)/7) as WeekInMonth,
Day(TempDate) As Day,
//InYearToDate(TempDate, $(vToday), 0) *-1 as CurYTDFlag,
//InYearToDate(TempDate, $(vToday), -1) *-1 as LastYTDFlag,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekYear(TempDate)& Num(week(TempDate)) as YearWeek,
WeekDay(TempDate) as WeekDay,
Num(WeekDay(TempDate)+1) as WeekDayNo,
Year2Date(TempDate, 0, 1, $(varMaxDate))*-1 AS CurYTD_Flag,
Year2Date(TempDate,-1, 1, $(varMaxDate))*-1 AS LastYTD_Flag,
InMonthToDate(TempDate, $(varMaxDate),0)*-1 AS CurMTD_Flag,
InMonthToDate(TempDate, $(varMaxDate),-1)*-1 AS LastMTD_Flag
Resident TempCalendar
Order By TempDate;
Drop Table TempCalendar;
Check the [AE Start Date], it seems that it might not be interpreted as date by QlikView (Why don’t my dates work?)
Also there is an extra parenthesis in the below line in MasterCalendar
Week(TempDate)+52 -Week(MonthStart(TempDate)) +1) as WeekinMonth,
maybe helpful, another calendar script without variables and temporary tables:
tabCalendar:
LOAD *,
Day(Date) as Day,
WeekDay(Date) as WeekDay,
Week(Date) as Week,
WeekName(Date) as WeekName,
Month(Date) as Month,
MonthName(Date) as MonthName,
Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,
QuarterName(Date) as QuarterName,
Year(Date) as Year,
WeekYear(Date) as WeekYear;
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1 <= MaxDate;
LOAD Min(Date) as MinDate,
Max(Date) as MaxDate
Resident tabSourceOfDate;
regards
Marco
Thank You. I tried to figure out My AE Date Format was incorrect as you said.
Thank You Marco. The code looks simple. I will use it for my future projects.