4 Replies Latest reply: Dec 6, 2017 4:05 AM by Gysbert Wassenaar RSS

    Date/time field bucket

    Jonnie Yacoub

      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;

        • Re: Date/time field bucket
          Gysbert Wassenaar

          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

            • Re: Date/time field bucket
              Jonnie Yacoub

              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
              A.M. van Keep

              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')