Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have used this code before with no problem, however the only difference is thatIi'm now using a CSV file with a field that has a Date in the format of 01JAN2014:00:00:00.
Should I be using something different when trying to create a master calendar off of a CSV?
Now i'm trying to use it in a new document and am getting the following errors:
Script line error:
TempCal:
LOAD
date(+rowno()-1 ) AS TempDate
AutoGenerate
- +1
as well as:
Error in expression:
')' expected
MasterCalendar:
LOAD Distinct
TempDate AS [Case 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,
InyearToDate(TempDate, ,0)*-1 As CurYTDFlag,
InYearToDate(TempDate, ,-1)*-1 AS LastYTDFlag
Resident
TempCal
Order By TempDate A
Here is my Master Calendar Creation Code:
// Call Master Calendar
MinMax:
LOAD
NUM ( MIN ( [case_created_date] )) AS MinDate,
NUM ( MAX ( [case_created_date] )) AS MaxDate
RESIDENT
AGG_CASES
;
// peek( fieldname [, row [, tablename ] ] )
LET vMinDateb = NUM(Peek ( 'MinDate', 0, 'MinMax' ));
LET vMaxDateb = NUM(Peek ( 'MaxDate', -1, 'MinMax' ));
LET vTodayb = $(vMaxDateb);
//**************************** Temporary Calendar - CALL ****************************
TempCal:
LOAD
date($(vMinDateb)+rowno()-1 ) AS TempDate
AutoGenerate
$(vMaxDateb) - $(vMinDateb) +1;
DROP TABLE MinMax;
MasterCalendar:
LOAD Distinct
TempDate AS [Case 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,
InyearToDate(TempDate, $(vToday),0)*-1 As CurYTDFlag,
InYearToDate(TempDate, $(vToday),-1)*-1 AS LastYTDFlag
Resident
TempCal
Order By TempDate ASC;
DROP TABLE TempCal;
Hi,
I think the date format is the issue, check the vMinDateb while loading it is empty. Replace below code with your code and check.
MinMax:
LOAD
NUM ( MIN (Timestamp#([case_created_date], 'DDMMMYYYY:hh:mm:ss') )) AS MinDate,
NUM ( Max(Timestamp#([case_created_date], 'DDMMMYYYY:hh:mm:ss') )) AS MaxDate
RESIDENT
AGG_CASES;
Regards,
Jagan.
Script line error:
TempCal:
LOAD
date(+rowno()-1 ) AS TempDate
AutoGenerate
- +1
as well as:
Error in expression:
')' expected
Are these '+','-' symbols intentional? Apart from that you are missing one semicolon (;) at the end. So try like:
TempCal:
LOAD
date(rowno()-1 ) AS TempDate
AutoGenerate 1;
Hi,
Can you attach some sample data?
Regards,
jagan.
Hi,
I think the date format is the issue, check the vMinDateb while loading it is empty. Replace below code with your code and check.
MinMax:
LOAD
NUM ( MIN (Timestamp#([case_created_date], 'DDMMMYYYY:hh:mm:ss') )) AS MinDate,
NUM ( Max(Timestamp#([case_created_date], 'DDMMMYYYY:hh:mm:ss') )) AS MaxDate
RESIDENT
AGG_CASES;
Regards,
Jagan.
Thanks Jagan!
It was the formatting issue.
Paul