Qlik Community

Qlik Sense Cloud Discussions

jonnieyacoub
Contributor

Date/time field bucket

Hi Qlikerss,

I have an field called [inserted] and the format of these fields are yyyy/mm/dd hh:mm:ss. Now i am trying to make an bucket of only the time but it is not really working. I have tried the following:

    IF(inserted >= 6.00 AND inserted < 7.00, DUAL ('6:00 / 7:00', 1),

    IF(inserted >= 7.00 AND inserted < 8.00, DUAL ('7:00 / 8:00', 2),

    IF(inserted >= 8.00 AND inserted < 9.00, DUAL ('8:00 / 9:00', 3),

    IF (inserted >= 9.00 AND inserted < 10.00, DUAL ('9:00 / 10:00', 4),

    IF (inserted >= 10.00 AND inserted < 11.00, DUAL ('10:00 / 11:00', 5),

    IF (inserted >= 11.00 AND inserted < 12.00, DUAL ('11:00 / 12:00', 6),

    IF (inserted >= 12.00 AND inserted < 13.00, DUAL ('12:00 / 13:00', 7),

    IF (inserted >= 13.00 AND inserted < 14.00, DUAL ('13:00 / 14:00', 8),

    IF (inserted >= 14.00 AND inserted < 15.00, DUAL ('14:00 / 15:00', 9),

    IF (inserted >= 15.00 AND inserted < 16.00, DUAL ('15:00 / 16:00', 10),

    IF (inserted >= 16.00 AND inserted < 17.00, DUAL ('16:00 / 17:00', 11),

    IF (inserted >= 17.00 AND inserted < 18.00, DUAL ('17:00 / 18:00', 12),

    IF (inserted >= 18.00 AND inserted < 19.00, DUAL ('18:00 / 19:00', 13),

    IF (inserted >= 19.00 AND inserted < 20.00, DUAL ('19:00 / 20:00', 14),

    IF (inserted >= 20.00 AND inserted < 21.00, DUAL ('20:00 / 21:00', 15),

    IF (inserted >= 21.00 AND inserted < 22.00, DUAL ('21:00 / 22:00', 16),

    IF (inserted >= 22.00 AND inserted < 23.00, DUAL ('22:00 / 23:00', 17),

    IF (inserted >= 23.00 AND inserted < 00.00, DUAL ('23:00 / 00:00', 18)

    ))))))))))))))))))

    AS lead_timestamp;

1 Solution

Accepted Solutions

Re: Date/time field bucket

If inserted is a date field then the unit of the field is a day, not an hour. Try using the Hour function:

DUAL(  Hour(inserted) & '.00 / ' &  If(Hour(inserted)=23,'00', Hour(inserted)+1) & '.00' ,Hour(inserted)-5) as lead_timestamp


talk is cheap, supply exceeds demand
4 Replies

Re: Date/time field bucket

If inserted is a date field then the unit of the field is a day, not an hour. Try using the Hour function:

DUAL(  Hour(inserted) & '.00 / ' &  If(Hour(inserted)=23,'00', Hour(inserted)+1) & '.00' ,Hour(inserted)-5) as lead_timestamp


talk is cheap, supply exceeds demand
avkeep01
Valued Contributor

Re: Date/time field bucket

Hi Jonnie,

you could try:

CLASS(TIME(inserted),'01:00:00') that will created class per hour. Then you'll need to change the texts in between.

=TIME(SUBFIELD(CLASS(inserted,'01:00:00'),' <= x < ',1),'hh:mm')

&' / ' &

TIME(SUBFIELD(CLASS(inserted,'01:00:00'),' <= x < ',2),'hh:mm')

jonnieyacoub
Contributor

Re: Date/time field bucket

Sir Wassenaar,

this worked thank you!! Could you just explain what the PICK function does? and what for the -5 does at the end?

    Pick(Hour(inserted)-5,

     DUAL ('6:00 / 7:00', 1),

     DUAL ('7:00 / 8:00', 2),

     DUAL ('8:00 / 9:00', 3),

     DUAL ('9:00 / 10:00', 4),

     DUAL ('10:00 / 11:00', 5),

     DUAL ('11:00 / 12:00', 6),

     DUAL ('12:00 / 13:00', 7),

     DUAL ('13:00 / 14:00', 8),

     DUAL ('14:00 / 15:00', 9),

     DUAL ('15:00 / 16:00', 10),

     DUAL ('16:00 / 17:00', 11),

     DUAL ('17:00 / 18:00', 12),

     DUAL ('18:00 / 19:00', 13),

     DUAL ('19:00 / 20:00', 14),

     DUAL ('20:00 / 21:00', 15),

     DUAL ('21:00 / 22:00', 16),

     DUAL ('22:00 / 23:00', 17),

     DUAL ('23:00 / 00:00', 18)

) as lead_timestamp;

Re: Date/time field bucket

See the online help: pick ‒ QlikView

Subtracting 5 from the hour value gets you the value you want the dual value to have: 6-5=1, 7-5=2, etc.


talk is cheap, supply exceeds demand
Community Browser