10 Replies Latest reply: Jul 8, 2015 4:37 AM by Sasidhar Parupudi RSS

    Re:Time

    Chanty 4u

      hi ,

       

      Need help.

       

      PFA

       

      my reuirment is from  ORDER_BOOK  field i want above 60min between10&30min,between 30&45min

       

       

      using if Else condition ony nt usng interval match fun

       

      Thank you

      suresh

        • Re:Time
          Manish Kachhia

          These 60 min, 10-30 min are duration?

          Provide some more info.

          • Re:Time
            Manish Kachhia

            If(Duration < 10, '<10',

                 If(Duration >= 10 and Duration < 30, '10-30',

                      If(Duration >= 30 and Duration < 45, '30-45',

                           '>=45')))

             

            or

             

            Aggr(If(Duration < 10, '<10',

                 If(Duration >= 10 and Duration < 30, '10-30',

                      If(Duration >= 30 and Duration < 45, '30-45',

                           '>=45'))), ORDER_BOOK) 

            • Re:Time
              Sasidhar Parupudi

              Please provide sample data

              • Re:Time
                Chanty 4u

                hi

                 

                i want in pivot table

                 

                PFA

                  • Re:Time
                    jagan mohan rao appala

                    Hi,

                     

                    Try this script

                     

                    LOAD SI_NUMBER,

                         HEADER_ID,

                         LOCATION_NAME,

                         REQ_CREATION_DATE,

                         ORD_CREATION_DATE,

                         OSN_TIME,

                         REQ_TO_ORD_CREATE_TIME_MINS,

                         ORDER_BOOK_TO_OSN_TIME,

                         TAT,

                         If(ORDER_BOOK_TO_OSN_TIME <10, '<10',

                      If(ORDER_BOOK_TO_OSN_TIME >= 10 and ORDER_BOOK_TO_OSN_TIME < 30, '10-30',

                              If(ORDER_BOOK_TO_OSN_TIME >= 30 and ORDER_BOOK_TO_OSN_TIME < 45, '30-45',

                                   '>=45'))) AS TimeRange

                    FROM

                    [C:\Users\gourus\Downloads\simswap2july.xlsx]

                    (ooxml, embedded labels, table is [between 10 & 30 minutes]);

                    Regards,

                    Jagan.

                  • Re:Time
                    jagan mohan rao appala

                    Hi,

                     

                    You can use If condition for this, IntervalMatch() is not suitable for this scenario.  Try like below

                     

                     

                    LOAD

                    *,

                    If(Duration <10, '<10',

                    If(Duration >= 10 and Duration < 30, '10-30',

                              If(Duration >= 30 and Duration < 45, '30-45',

                                   '>=45'))) AS TimeRange

                    FROM DataSource;

                     

                    Hope this helps you.

                     

                    Regards,

                    Jagan.

                    • Re:Time
                      Sasidhar Parupudi

                      if (ORDER_BOOK_TO_OSN_TIME<10,'less than 10 minutes',if(ORDER_BOOK_TO_OSN_TIME>=10 and ORDER_BOOK_TO_OSN_TIME<=30,'between 10 & 30 minutes',if(ORDER_BOOK_TO_OSN_TIME>30 and ORDER_BOOK_TO_OSN_TIME<=45,'between 30 & 45 minutes',if(ORDER_BOOK_TO_OSN_TIME>45 and ORDER_BOOK_TO_OSN_TIME<=60,'between 45 & 60 minutes',if(ORDER_BOOK_TO_OSN_TIME>=60 ,'above 60 minutes'))))) as Interval