Below script will tell you how to create Master Calendar from Min and Max TimeStamp.
Load * Inline
MIN(NUM(DATE(FLOOR(Timestamp#(DateField)),'DD/MM/YYYY'))) as MinDate,
MAX(NUM(DATE(FLOOR(Timestamp#(DateField)),'DD/MM/YYYY'))) as MaxDate
Let vMin = NUM(Peek('MinDate',0,'MinMax'));
Let vMax = NUM(Peek('MaxDate',0,'MinMax'));
Now you can create
Month, Year, WeekDay, Day, MonthYear, Quarter etc...
Drop Table MinMax;
date ($(vMin) + rowno() -1) as Date
The first thing is to confirm if the loaded data format matches your format variables in QlikView. If the a loaded value for example is '25/06/2010 11:22:53' and your application has the timestamp format 'DD/MM/YYYY HH:MM:SS', then QlikView will automatically interpret the value as a timestamp.
If this is the case then the stored value would be a timestamp, meaning that it has an underlying numerical value that is a decimal value. In the decimal value the integer part represents the date and the decimal part represents the time. In order to convert such value to a date you need to first eliminate the decimal part and then format the value to a date.
If the loaded value does not match your timestamp format then the value will be loaded as text. In this case you can create the date value directly by using the Date functions. In this case you must specify the incoming time stamp format, so that the Date#() function knows how to parse the loaded text value.
Date(Floor(TimeStamp#(Value, 'DD/MM/YYYY hh:mm:ss')))
There is never a need to convert a Date or Timestamp value to a number in order to use it in aggregations or for example find the largest or smallest value. The simple reason for this is that a properly formatted Date or Timestamp value always has an underlying numerical value. The important part is to make sure that the underlying value for a Date does not have a decimal part and that a Time value does not have a integer part, while a Timestamp value is expected to have an underlying decimal value as it's numerical value.
To find out how QlikView interpreted the incoming value, you can use the Num() function to reveal the underlying numerical value,
Thank you for explaining it in detail, however I don't seem to be able to apply it to my code, please have a look at my test code and point me in the right direction.
test.qvw 160.8 K
Sorry for the late reply katarzyna.cwik. Please see the attached example as a suggestion for changes and with some observations.
The DateTime table probably should have a defined key field, which is formatted as Date. This will allow for your calendar table to properly link to the data. It is commonly good to indicate which field are key field, so that you more easily can avoid using them in expressions or dimensions. The HidePrefix system variable increases the invisibility of the key field, by not showing them in the field lists inside the application interface.
In the MinMax table I would recommend removing the format variable as second parameter in the Date() function. There is also no need to format the value as a TimeStamp, since the values are already properly formatted in the DateTime table.
With the data properly formatted as Dates in the MinMax table, there is no longer a need for additional format when you generate the variable values. The variable will now be Date formatted in the same way as the Peeked table MinMax.
In the TempCal table, I would recommend using $(#variable) expansion to expand the actual numerical value from the variables dual value. For a date value this means that you will not expand the visual date like 17/06/2012, instead the expansion will expand the numerical value 41077. When you are interested in using the actual numerical value of a variable, always use the $(#variable) expansion and you will never have to consider the actual visual format of the variable.
Always keep in mind that the dollar expansion is done before the script line is executed, which means that you can evaluate the dollar expansion result in the script reload log;
QlikView desktop client > Settings > Document Properties > General > Generate Logfile
Finally in the Master Calendar table the key field needs to be altered to match the key created in the DateTime table. Notice that the key field %Date is also stored in identical field called Date. The reason for this is to allow calculations of usage of the data in the key field. Always avoid calculations on a key field, since this actually refers to two tables and the outcome of the data can be a bit unpredicable.
Hope this helped you progress with your project.
test (3).qvw 170.0 K