Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a requirement to create time bucket like 8:30 to 9:30, 9:30 to 10:30,10:30 to 11:30......my date format is like 01/01/2016 4:30:10 AM. How to achieve this?
Thanks,
Hi,
Try like this
LOAD
*,
If(Minute >= 30, Hour & ':30 - ' & (Hour + 1) & ':30', (Hour - 2) & ':30 - ' & (Hour - 1) & ':30') AS TimeRange;
LOAD
*,
Hour(TimeStamp) AS Hour,
Minute(TimeStamp) AS Minute;
LOAD
TimeStamp#(TimeStamp, 'MM/DD/YYYY hh:mm:ss TT') AS TimeStamp
INLINE [
TimeStamp
01/01/2016 1:30:10 AM
01/01/2016 4:30:10 AM
01/01/2016 5:30:10 AM
01/01/2016 5:45:10 AM
];
Regards,
Jagan.
I would suggest creating a time field in your application
LOAD *,
If(Time >= Time#('08:30 AM', 'hh:mm TT') and Time < Time#('09:30 AM', 'hh:mm TT') , Dual('8:30 to 9:30', 1),
If(Time >= Time#('09:30 AM', 'hh:mm TT') and Time < Time#('10:30 AM', 'hh:mm TT') , Dual('9:30 to 10:30', 2),
If(Time >= Time#('10:30 AM', 'hh:mm TT') and Time < Time#('11:30 AM', 'hh:mm TT') , Dual('10:30 to 11:30', 3),
LOAD TimeStamp,
Time(Frac(TimeStamp)) as Time,
Date(Floor(TimeStamp)) as Date
....
=if(Time='8:30 to 9.30' B1, if(Time=9.30 to 10.30 B2, 'B3')) As newBucket
I'd use something like this for the bucket calculation:
=timestamp(ceil(MyTimeField, 1/24, 1/48))
- rounding the time up to the level of 1 hour (1/24), with the offset of 30 minutes (1/48)
cheers,
Oleg Troyansky
check out my new book QlikView Your Business!
Hi,
Try like this
LOAD
*,
If(Minute >= 30, Hour & ':30 - ' & (Hour + 1) & ':30', (Hour - 2) & ':30 - ' & (Hour - 1) & ':30') AS TimeRange;
LOAD
*,
Hour(TimeStamp) AS Hour,
Minute(TimeStamp) AS Minute;
LOAD
TimeStamp#(TimeStamp, 'MM/DD/YYYY hh:mm:ss TT') AS TimeStamp
INLINE [
TimeStamp
01/01/2016 1:30:10 AM
01/01/2016 4:30:10 AM
01/01/2016 5:30:10 AM
01/01/2016 5:45:10 AM
];
Regards,
Jagan.
Also check this link