Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm having trouble getting a calendar to work in my model.
I have the script for making a calendar from a previous model, but I think there are some changes I need to make to the scrpt that I am overlooking.
Here is the script I have for the calendar:
//*************** Get Dates from Data ***************
DateTemp:
LOAD Date(Date#(Timestamp,'YYYYMMDD'),'DD/MM/YYYY') as Timestamp
RESIDENT CallLogs
ORDER BY Timestamp Asc;
LET vMinDate = Num(Peek('Timestamp', 0, 'DateTemp')); //LET ... evaluates the expression on the right side of the ’=’ before it is assigned to the variable
LET vMaxDate = Num(Peek('Timestamp', -1, 'DateTemp'));
LET vToday = Num(today());
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(vMinDate)+IterNo()-1 AS Num,
Date($(vMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE ($(vMinDate)+IterNo()-1) <= $(vMaxDate);
//*************** Master Calendar ***************
MasterCalendar:
LEFT KEEP (CallLog)
LOAD TempDate AS %_DateKey,
TempDate as Date,
week(TempDate) AS Week,
year(TempDate) AS Year,
month(TempDate) AS Month,
day(TempDate) AS Day,
weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate)/3) 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,
num(month(TempDate)) as Period
RESIDENT TempCalendar
ORDER BY TempDate Asc;
Drop table TempCalendar;
Drop table DateTemp;
Hi,
I think you have to change this TempDate as Date to TempDate as Timestamp then you can able to join.
MasterCalendar:
LEFT KEEP (CallLog)
LOAD TempDate AS %_DateKey,
TempDate as Date,
week(TempDate) AS Week,
year(TempDate) AS Year,
month(TempDate) AS Month,
day(TempDate) AS Day,
weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate)/3) 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,
num(month(TempDate)) as Period
RESIDENT TempCalendar
ORDER BY TempDate Asc;
Celambarasan
i changed
TempDate as Date
to
TempDate as Timestamp
BUT i still get the following error messages when Reloading:
Field not found
DateTemp:
LOAD Date(Date#(Timestamp,'YYYYMMDD'),'DD/MM/YYYY') as Timestamp
RESIDENT CallLogs
ORDER BY Timestamp Asc
Field not found - <<=>
TempCalendar:
LOAD
+IterNo()-1 AS Num,
Date(+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE (+IterNo()-1) <=
Table not found
MasterCalendar:
LEFT KEEP (CallLog)
LOAD TempDate AS %_DateKey,
TempDate as Timestamp,
week(TempDate) AS Week,
year(TempDate) AS Year,
month(TempDate) AS Month,
day(TempDate) AS Day,
weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate)/3) as Quarter,
date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
week(TempDate)&'-'&Year(TempDate) AS WeekYear,
Year2Date(TempDate, 0, 1, 40966)*-1 AS CurYTDFlag,
Year2Date(TempDate,-1, 1, 40966)*-1 AS LastYTDFlag,
num(month(TempDate)) as Period
RESIDENT TempCalendar
ORDER BY TempDate Asc
Table not found
DROP TABLES statement
Table not found
DROP TABLES statement
Hi,
can you paste code for your CallLogs table?
Hi,
Did you have Timestamp field in CallLogs table?Problem came because of that only.
Celambarasan
//-------------------------------------------------------------
//This is the CallLogs data from the CallLogs QVD
//-------------------------------------------------------------
CallLogs:
LOAD agentcode,
agentname,
callduration,
callid,
id,
profileno,
ramsno,
statedescription,
timestamp
FROM
C:\Users\Chris\TRAINING\CustomerCallLogs\QVDs\CallLogs.qvd
(qvd);
Hi,
Check with this for DateTemp table.
DateTemp:
LOAD Date(Date#(timestamp,'YYYYMMDD'),'DD/MM/YYYY') as TimeStamp
RESIDENT CallLogs
ORDER BY TimeStampAsc
Celambarasan
Try,
DateTemp:
LOAD Date(Date#(timestamp,'YYYYMMDD'),'DD/MM/YYYY') as timestamp
RESIDENT CallLogs
ORDER BY timestamp asc;
LET vMinDate = Num(Peek('timestamp', 0, 'DateTemp')); //LET ... evaluates the expression on the right side of the ’=’ before it is assigned to the variable
LET vMaxDate = Num(Peek('timestamp', -1, 'DateTemp'));
LET vToday = Num(today());
I think spell mistake
LEFT KEEP (callLogs)
thanks,
Mukram
and
in callLogs timestamp, so use t small letter in DateTemp Table.