There are some differences if you are using date and date time into Qlikview.
In QlikView each date are represented by a numeric value for Example:
| Loaded Value |
Expression to show in GUI |
Value in QlikView |
| 01.01.1990 |
=num(date('01.01.1990')) |
32874 |
| 01.01.2014 |
=num(date('01.01.2014')) |
41640 |
| 01.01.1990 00:10:00 |
=num(date('01.01.1990 00:10:00')) |
32874,006944444 |
| 01.01.2014 00:10:00 |
=num(date('01.01.2014 00:10:00')) |
41640,006944444 |
As you can see there are also a Difference between Integer and Float Values which creating in QlikView.
Now if you are creating a Master Calendar and only have regular Dates like the first Examples above or described in Point 1 bellow, you will not running into any issue.
But in Case of you having a Date with Time you will running into an Issue and also generate a huge amount of Records in the Master Calendar.
Why does this happen?
As you can see in the Example bellow (Point 3) we are generate Dates between a Min and Max Value:
MyDateField:
Load date($(vMinDatum)+Iterno()-1) As MyDateField
Autogenerate 1 While $(vMinDatum)+Iterno()-1<= $(vMaxDatum);
If you are using Regular Date the Step width are 1 Day (integer 41640 +1) but we are using a Float Value like 41640
,006944444 what mean that you are counting on the last digit now and get 41640
,006944445 as next value. That mean that you are generate a record for each millisecond what mostly not needed for a Master Calendar.
The other Problem you will getting if you using the same Field as Keyfield to link the Master Calendar and the MainTable where the dates come from what can also cause an mussmatch in the data.You can easy resolve this issue by Loading the Datefield with the dayStart() Function see Point 2b bellow.
To make sure that you are always be on the right side we are recommend to using this way always, no role if you are using Date or Datetime you will get the correct result.
Resolution:
1. Date and Time Examples which loading into QV:
| Regular Dates without time Values |
| 01.12.2014 |
12/01/2014 |
01-12-2014 |
| Regular Dates with time values |
| 01.12.2014 00:01:33 |
12/01/2014 00:01:33 |
01-12-2014 00:01:33 |
2 a. You are loading regular Dates without any Time Values
Temp:
LOAD HeaderID,
Starttime,
FROM
QVTestProd.xlsx
(ooxml, embedded labels, table is Tabelle1);
MyData:
Load *,
date(Starttime,'DD.MM.YYYY') As myCalendarKeyField
Resident Temp Order by Starttime Asc;
Drop table Temp;
2 b. You are loading regular Dates with Time Values
Temp:
LOAD HeaderID,
Starttime As OrigStarttime,
dayStart(Starttime) as Starttime
FROM
QVTestProd.xlsx
(ooxml, embedded labels, table is Tabelle1);
MyData:
Load *,
date(Starttime,'DD.MM.YYYY') As myCalendarKeyField
Resident Temp Order by Starttime Asc;
Drop table Temp;
3. Master Calendar Script Example:
/******************** Table MinMaxDate *******************/
Tmp_MinMaxDatum:
Replace Load
Min(date#(myKalenderGenField)) As MinDatum,
Max(date#(myKalenderGenField)) As MaxDatum
Resident MeineDaten;
/******************** Set Vars *******************/
Let vMinDatum = Peek('MinDatum', 0, 'MinMaxDatum') ;
Let vMaxDatum = Peek('MaxDatum', 0, 'MinMaxDatum') +1 ;
Drop Table Tmp_MinMaxDatum;
MyDateField:
Load date($(vMinDatum)+Iterno()-1) As MyDateField
Autogenerate 1 While $(vMinDatum)+Iterno()-1<= $(vMaxDatum);
MasterCalendar:
Load *,
MyDateField As myCalendarKeyfield,
MyDateField As RegularDate,
Year(MyDateField) As CalYear,
Month(MyDateField) As CalMonth,
'Q' & Ceil(Month(MyDateField) / 3) As CalQuarter,
Day(MyDateField) As CalDay
Resident MyDateField
Order by Datumsfeld Asc;
Drop Table MyDateField;
