Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a challenge regarding dateissue. I have written a qvd file so that I could read what the output data is like, and the result was a bit devostating!
I have had major issues with the dates showing up in a different format
31.12.2008 23:59:59:000
31.12.2009 23:59:59:000
31.12.2010 23:59:59:000
31.12.2011 23:59:59:000
31.12.2012 23:59:59:000
But looking at the qvd file I see more issues:
These are my scripts:
MyTable:
LOAD Date(Floor([Posting Date]),'YYYY-MM-DD') as [Posting Date]
FROM $(vG.DatasourceQVDPath)\XXXXXXXX_Drift.qvd (qvd);
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min([Posting Date]) as minDate,
max([Posting Date]) as maxDate
Resident MyTable;
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 [Posting Date],
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,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
STORE MasterCalendar into $(vG.QVDPath)MasterCalendar.QVD;
Drop Table TempCalendar;
STORE MyTable into $(vG.QVDPath)PostingDate.QVD;
