Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am trying to create a master calender for my data ,
I have a table in which time_stamp is the date field, the code i am having for master calender is given below , my qlikview table is based of on a sql query select statement , if i run the sql query , i get the chart display with a graph , based on my time_stamp values but as there are some dates missing in the time_stamp , i thought of getting the master calender to get all dates displyed inll formats , and for the dates on which there is no data the graph shows blank ..
But when i use the date values from master calender in the graph , it does not seem as all the metrics are calculated based on the time_stamp
How do i assign time_stamp to this calender ,
Calendar:
LET
vDateMin = Num(MakeDate(2009,1,1));LET
vDateMax = Floor(MonthEnd(Today()));LET
vDateToday = Num(Today());TempCalendar:
LOAD
$(vDateMin)
+ RowNo() - 1 AS DateNumber,
Date
($(vDateMin) + RowNo() - 1) AS Time_stampAUTOGENERATE
1WHILE
$(vDateMin)+IterNo()-1<= $(vDateMax);Calendar:
LOAD
Date
(Time_stamp) AS CalendarDate,
// Standard Date Objects
Day
(Time_stamp) AS CalendarDayOfMonth,
WeekDay
(Time_stamp) AS CalendarDayName,
Week
(Time_stamp) AS CalendarWeekOfYear,
Month
(Time_stamp) AS CalendarMonthName,
'Q'
& Ceil(Month(Time_stamp)/3) AS CalendarQuarter,
Year
(Time_stamp) AS CalendarYear,
// Calendar Date Names
WeekName
(Time_stamp) as CalendarWeekNumberAndYear,
MonthName
(Time_stamp) as CalendarMonthAndYear,
QuarterName
(Time_stamp) as CalendarQuarterMonthsAndYear,
// Start Dates
DayStart
(Time_stamp) as CalendarDayStart,
WeekStart
(Time_stamp) as CalendarWeekStart,
MonthStart
(Time_stamp) as CalendarMonthStart,
QuarterStart
(Time_stamp) as CalendarQuarterStart,
YearStart
(Time_stamp) as CalendarYearStart,
// End Dates
DayEnd
(Time_stamp) as CalendarDayEnd,
WeekEnd
(Time_stamp) as CalendarWeekEnd,
MonthEnd
(Time_stamp) as CalendarMonthEnd,
QuarterEnd
(Time_stamp) as CalendarQuarterEnd,
YearEnd
(Time_stamp) as CalendarYearEnd,
// Combo Date Examples
'Q'
& Ceil(Month(Time_stamp)/3) & '/' & Year(Time_stamp) AS CalendarQuarterAndYear,
Year
(Time_stamp) & '/' & 'Q' & Ceil(Month(Time_stamp)/3) AS CalendarYearAndQuarter,
'Wed '
& DayStart(WeekStart(Time_stamp) + 3) as CalendarWednesdaysRESIDENT
TempCalendar ORDER BY Time_stamp ASC;DROP
TABLETempCalendar;
LET
vDateMin = Num(MakeDate(2000,1,1));LET
vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));LET
vDateToday = Num(Today());
The below is based on a Field "OrderDate" in the Orders table
The TempCalendar is used to generate all the date between the values
VarMinDate and VarMaxDate.
LET varMinDate = Num(Peek('OrderDate', 0, 'Orders'));
LET varMaxDate = Num(Peek('OrderDate', -1, 'Orders'));
LET vToday = num(today());
TempCalendar:
LOAD
$(varMinDate)+IterNo()-1 AS Num,
Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE (1) WHILE $(varMinDate)+IterNo()-1<=
$(varMaxDate);
//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS OrderDate,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
applymap('Quarters_Map', num(month(TempDate)),
null()) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS
MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS
CurYTDFlag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS
LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;
Does the ordetdate field has time value in it ? is it a datetime field?
I get an script error saying :
Field not found - <<=>
TempCalendar:
LOAD
+IterNo()-1 AS Num,
Date(+IterNo()-1) AS TempDate
AUTOGENERATE (1) WHILE +IterNo()-1<=
Here is my script, i just added the tempcalender block to my exiting script which worked fine :
LET varMinDate = Num(Peek('TIME_STAMP', 0, 'RumiUtilization'));
LET varMaxDate = Num(Peek('TIME_STAMP', -1, 'RumiUtilization'));
LET vToday = num(today());
TempCalendar:
LOAD
$(varMinDate)+IterNo()-1 AS Num,
Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE (1) WHILE $(varMinDate)+IterNo()-1<=
$(varMaxDate);
//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS TIME_STAMP,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
applymap('Quarters_Map', num(month(TempDate)),
null()) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS
MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS
CurYTDFlag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS
LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;
RumiUtilization:
LOAD
RUMI_DEVICE,
PROJECT,LOCATION,TIME_STAMP,Util_rate;
sql select ---- // all my sql select conditions
outer join(RumiUtilization) // loading from excel
LOAD
*
FROM
C:\Users\user\Desktop\test.xlsx
(ooxml, embedded labels, table is Location_ALL);
outer join(RumiUtilization)
LOAD
*
FROM
C:\Users\user\Desktop\test..xlsx
(ooxml, embedded labels, table is Project_ALL);