topic Timestamp as a Number - How many Decimal Places? in QlikView

Timestamp as a Number - How many Decimal Places?

Fri, 29 May 2015 09:22:35 GMT

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?

Re: Timestamp as a Number - How many Decimal Places?

Gysbert_Wassenaar — Fri, 29 May 2015 15:23:00 GMT

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

Re: Timestamp as a Number - How many Decimal Places?

MarcoWedel — Fri, 29 May 2015 15:28:07 GMT

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

Re: Timestamp as a Number - How many Decimal Places?

swuehl — Fri, 29 May 2015 15:41:24 GMT

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

Maybe try this approach taken from Rob's blog:

Correct Time Arithmetic | Qlikview Cookbook

Re: Timestamp as a Number - How many Decimal Places?

Mon, 01 Jun 2015 13:13:50 GMT

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.

Re: Timestamp as a Number - How many Decimal Places?

Mon, 01 Jun 2015 13:19:58 GMT

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