Timestamp as a Number - How many Decimal Places?

[#1]

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?

[Gysbert_Wassenaar]

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);

[MarcoWedel]

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

[swuehl · Accepted solution]

Comparing floating point values can make you a hard time...

Maybe try this approach taken from Rob's blog:

Correct Time Arithmetic | Qlikview Cookbook

[#5]

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.

[#6]

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...