Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Timestamp as a Number - How many Decimal Places?

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

MasterCal.JPG

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?

1 Solution

Accepted Solutions
MVP
MVP

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

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 Replies
MVP & Luminary
MVP & Luminary

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

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


talk is cheap, supply exceeds demand

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

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

MVP
MVP

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

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

Maybe try this approach taken from Rob's blog:

Correct Time Arithmetic | Qlikview Cookbook

Not applicable

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

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.

Not applicable

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

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