Think this shouldn't be a difficult one ... but just can't find the correct answer ...
I have a load scipt containing a DateTime value
LOAD .... modificationdatetime, ... FROM ...
I also have a calendar to which I want the modificationdatetime point to ... Sounds easy?
LET vMinDate = NUM(DATE('1-1-2004')); LET vMaxDate = NUM(TODAY());
TempCalendar: LOAD $(vMinDate)+ITERNO() AS NUM, DATE($(vMinDate)+ITERNO()-1) AS TempDate
AUTOGENERATE (1) WHILE $(vMinDate)+ITERNO()-1 <= $(vMaxDate);
ModificationDateCalendar: LOAD TempDate AS modificationdatetime, YEAR(TempDate) AS Year, APPLYMAP('Quarter', NUM(MONTH(TempDate))) AS Quarter, MONTH(TempDate) AS Month, WEEK(TempDate) AS Week, WEEKDAY(TempDate) AS WeekDay, DAY(TempDate) AS Day, DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear
RESIDENT TempCalendar ORDER BY TempDate; DROP TABLE TempCalendar;
Does not seem to be wrong ...
But now a way to find to get the correct date connection.
I tend to use floor() myself, but if I weren't already in that habit, the daystart() function would probably be a slightly better choice. Same exact numeric result, but floor() is a mathematical function that takes advantage of QlikView's internal formats, while daystart() is a date/time function that doesn't depend on the internal format. Very minor point overall, though, as I'm pretty confident that QlikView will never change the way they represent dates and times.