2 Replies Latest reply: Aug 15, 2017 2:45 AM by Pankaj Thakur RSS

    hour format conversion

    Priyabrata Das

      Hi,

       

      I want to convert duration field which is in "1days0hrs1min 13sec" format to [ <5mins, 5mins-30mins, 30 – 1hr,1hr-2hr,2hr-3hr,>3hr] format.

        • Re: hour format conversion
          Kaushik Solanki

          Hi,

           

          Try this code.

           

          Load *,if(TimeinMinutes < 5,'<5 Minutes',

            If(TimeinMinutes >= 5 and TimeinMinutes < 30,'5 - 30 Minutes',

            If(TimeinMinutes >= 30 and TimeinMinutes < 60,'5 - 1 Hour',

            If(TimeinMinutes >= 60 and TimeinMinutes < 120,'1 - 2 Hour',

            If(TimeinMinutes >= 120 and TimeinMinutes < 180,'2 - 3 Hour','> 3 Hour'))))) as Bucket;

          LOad *,(DAYS * 24 * 60) + Hours + (Minutes / 60) + (Seconds / 3600) as TimeinMinutes;

          Load XYZ,SubField(XYZ,'days',1) as DAYS,TextBetween(XYZ,'days','hrs') as Hours,TextBetween(XYZ,'hrs','min') as Minutes,TextBetween(XYZ,'min ','sec') as Seconds inline [

          XYZ

          1days0hrs1min 13sec

          0days18hrs30min 10sec

          ];

           

          Regards,

          Kaushik Solanki

          • Re: hour format conversion
            Pankaj Thakur

            Hi Priyabrata,

            You can even try using interval match.

            PFA Solution.

             

            Regards,

            Pankaj