Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
markp201
Contributor III

Decimal time in whole seconds...

With date/time fields, the approach is to break them up using something like

Date(dateandtime) for the date

frac(dateandtime) for the time

because for 10 years of transactions, there could be 315,000,000 distinct values for each date/time - wowzers batman!!

If the time is hours, minutes, seconds this solution will work but sometimes the field may include fractional seconds because internally the date/time is just a decimal number (date is offset from a base - usually 1/1900 and the time is simply seconds/86400).  If you don't need the fractional part of the time there is any easy way to get rid of it.

1/86400 is approximately 0.000012 so you only need 6 decimal places for any second of the day.  When breaking up the time use the following

FLOOR(FRAC(dateandtime)*86400)/86400

All it does it remove all the "junk" beyond the 6th decimal place and round to the nearest second.  It will likely get your distinct values down considerably.

Tags (3)