Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am attempting to build a Master Calendar at an Hourly granularity, using a timestamp as the joining Key. The key value is generated via:
LOAD
($(vMinDate) + (rowno()*(1/24)) -1) as TempDateTime
AutoGenerate
((($(vMaxDate))-$(vMinDate) +1)*24);
The Data in the Fact table generates the equivalent numeric timestamp value thus:
floor(num#( DATETIME),1/24) as [DateTimeKey],
The data is from an Oracle Database originally, but is loaded from QVD at this stage.
However, This key value is sometimes failing to associate properly - the generated numeric values look correct, but QV is failing to make the association....
nb. I have forced the DateTimeKey column to display as numeric
So, is the value generated in the calendar generated to a different number of decimal places to the one generated by the floor function? Would this be fixed by rounding one or both of them?
Comparing floating point values can make you a hard time...
Maybe try this approach taken from Rob's blog:
It's certainly possible that the generated fractions differ. See this blog post: Rounding Errors
You could try using the floor function on the generated timestamps as well:
LOAD
floor(($(vMinDate) + (rowno()*(1/24)) -1),1/24) as TempDateTime
AutoGenerate
((($(vMaxDate))-$(vMinDate) +1)*24);
Hi,
I once had a similar issue and used a round(timestamp, 1/24) function instead in order to get the nearest full-hour timestamp.
hope this helps
regards
Marco
Comparing floating point values can make you a hard time...
Maybe try this approach taken from Rob's blog:
I really need to retain the floor functionality, rather than round. However attempting to wrap the Calendar in the floor function:
floor(num#(TempDateTime),1/24) AS [DateTimeKey],
yields some very strange results, so I am not going to continue investigating this method..
Thanks for the suggestions though, much appreciated.
So, as suggested in the cookbook article, wrapping the values in the timestamp#(timestamp()) pair of functions seems to work - although it needs to be done on both the Fact table as well as the Calendar...