I have a problem:
I have a series of events that occur within a number of milliseconds of each other.
How do I show all the timestamps that occur within the same MINUTE - Perhaps I need to do it in SQL before it gets to QlikView?
maybe with a new field rounded at the minute
timestamp(today() + rand()) as ts
Left Join (Z)
timestamp(round(ts, 1/(24*60))) as tsmin
sorry it has taken so long to respond:
|Date(Floor(CourseStartDateTime), 'dd/MM/yyyy h:mm:ss TT') as noSeconds,|
Date(CourseStartDateTime,'dd/MM/yyyy h:mm') AS CourseStartDateTimeA,
this will give me the day as a selection
so in order to sort by date and then by ID but without taking into account the milliseconds I can use Date(Floor(CourseStartDateTime), 'dd/MM/yyyy h:mm') as noSeconds
BUT ... what if i actually want to sort within minutes - because has this just taken away ALL the hours and minutes?
I would create separate date and time values. This will reduce the number of distinct values which will save space and improve performance:
Date(Floor(CourseStartDateTime)) as Date,
Time(Round(Frac(CourseStartDateTime), 1/(24*60))) as Time
Then use the Time field to sort in minutes.
Hi Jonathan and Sunny
I don't think I am explaining the problem well.
I think I can explain it better by:
if I use dayName(CourseStartDateTime) - this groups all the timestamps which occur on the one day together. I want to know if there is some other function or formula that I can use that will group all timestamps that happened within the same hour together. Or that happened within the same second together.
Although thinking further this will not help those instances when a group of activities will take place say from 10:59 followed by 11:00.
but maybe I should try to do the conversion in sql???
Lets say you have a timestamp like 2016-09-02 17:01:25.123
Then you can use functions such as
Hour(timestamp) -> 17
Minute(timestamp) -> 1
Second(timestamp) -> 25
Date(timestamp) -> Dual('2016-09-02', Num(timestamp))
Time(timestamp) -> Dual('17:01', Num(timestamp))
For the Date() and Time() you may note that the field still contains the full numeric representation of the timestamp, but added a string representation to represent the timestamp in charts and list boxes. This object is however different to another timestamp from the same Date with the same representation, which means that it may appear as if a list box contains the same date several times.
This is why you get a lot of recommendations to truncate the timestamps. Qlikview is the most efficient when you have few unique values, and timestamps stored at a millisecond level are rarely unique.
Say for instance you want to use the Time() representation, but round the timestamp to minute level. In that case you may use the Floor() function to round down.
Time(Floor(timestamp, 1/(24*60))) -> Dual('17:01:', rounded timestamp)
or for better readability as
Time(Floor(timestamp, Interval#('1', 'm'))) -> Dual('17:01:', rounded timestamp)
Which is what I tend to prefer as I don't have to do any calculations to see that it is floored to a minute level.