Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Example, map:
11:23:32 --> 11:00
11:24:18 --> 11:00
12:01:18 --> 12:00
12:02:17 > 12:00
So I could quickly filter on events taking place within one same hour
Create hours like:
Time(TimeStamp#(YourField, 'hh:mm:ss') , 'hh')
Something like below
=Time(Time# ('11:23:32','hh:mm:ss'),'hh')
or
=Time(Time# (YourTimeStamp,'hh:mm:ss'),'hh')
You have to write
=Time(Time# ('11:23:32','hh:mm:ss'),'hh')&':00'
=Time(Time# ('11:24:18','hh:mm:ss'),'hh')&':00'
=Time(Time# ('12:01:18','hh:mm:ss'),'hh')&':00'
=Time(Time# ('12:02:17','hh:mm:ss'),'hh')&':00'
Regards
What you want to keep in mind here is that a timestamp value and a time value is always represented by an underlying decimal value. In the decimal value the integer part represents the date, and the decimal component represents the time.
By using the Time() and Time#() functions you only alter the text part of the value, which means that the underlying numerical value will not be limited.
For example if we look at the time 11:23:32 the numerical value is 0.47467592... which can be retrieve by using the Num() function.
=Num(Time#('11:23:32'))
Now compare that with the time 11:00, by again using the Num() function, and it is apparent that the expected underlying numerical value for these two times are different. 11:00 corresponds to 0.458333...
=Num(Time#('11:00', 'hh:mm'))
Why is it important to consider the underlying numerical value?
The simple reason is that selections in QlikView are primarily applied in the underlying numerical value.
There can be several ways to tackle this issue in your data model, but one way is to extract the hour value and use it as base for creating time value.
=Time#(Hour(Time#( '11:23:32')), 'hh')
The numerical value is also as expected 0.45833...
=Num(Time#(Hour(Time#( '11:23:32')), 'hh'))
Try
HOUR(TimeStamp) as Hour
Regards
Alan
Hour() will only return the hour digit, not an actual time representation.