I'm having some issues to create a master calendar from a field of a table connected via Mssql.
The data in the DB is set as Char (8) and format is YYYYMMDD
The error is "
SQL##f - SqlState: 37000, ErrorCode: 102, ErrorMsg: [Microsoft][SQL Server Native Client 10.0][SQL Server]Sintassi non corretta in prossimità di 'MAPPING'.
sql select * from p2wsal00"
ODBC CONNECT32 TO PITECOEVO (XUserId is TIMXBYFNULZGWXVMWbbaK, XPassword is VPQEPYFGUbMGGYMA);
SALDI:
LOAD
date(date# (DTAL,'YYYYMMDD'), 'DD-MM-YYYY') AS DATA,
SOCIETA,
CODICERC,
SALCONZ AS SALDO;
sql select * from p2wsal00
and for the Master calendar:
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(DTAL) as minDate,
max(DTAL) as maxDate
Resident SALDI;
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 DATA,
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,
