5 Replies Latest reply: Feb 3, 2017 1:12 AM by Agrim Sharma RSS
      • Re: how to convert values into duration?
        Agrim Sharma

        can any one help?

        • Re: how to convert values into duration?
          bruno bertels

          Hi agrim

           

          I'am not a specialist but may be this can help you.

           

          First , convert duration in time or days or week or months etc  is not very easy

           

          Your SLA dimension is a text field

          So the approach here is

          1 defined if your text string is about number of  hours or number of days

          so you can test it like this :

           

          //if there is the word 'days' in your field then it's 'DAYS' , if not so it's HOURS

          if(WildMatch(SLA,'*days*'),'DAYS','HOURS')

           

          if it is DAYS :

          //you can use interval function

          interval(SubField(SLA,' ',1),'D / H ')

           

          if it is HOURS :

          //you can use this one :

          TIME(

          interval(SubField(SLA,' ',1),'hh:mm ')/24,'hh:mm')

           

          So you can test this in hour script :

           

          Temp0:

          load*Inline [SLA,

          2 working days,

          4 h Office,

          4 h Office,

          4 h Office,

          4 h Office,

          2 working days,

          3 working days,

          3 working days,

          5 working days,

          2 h Office,

          8 h Office,

          12 h Office,

          ];

           

          temp:

          load

          *,

          if(WildMatch(SLA,'*days*'),'JOUR','HEURE') as Type,

           

          if(WildMatch(SLA,'*days*'),

          interval(SubField(SLA,' ',1),'D jour H ')

          ,

          TIME(

          interval(SubField(SLA,' ',1),'hh:mm ')/24,'hh:mm')

          ) as duration

           

          resident Temp0;

          drop table Temp0;

           

          With this script i can make this strait table :

           

           

           

          You did not precise the format of the duration so i add it lke this :

          1 column Dimension SLA

          2 column : count the different type of SLA

          3 column : Type : evaluate if it's dealing with days or hours

          4 column : take the value in the field with subfield function

          5 column : calculate the Hours if column 2 = HEURE

          6 column : calculate the Days if column 2 = JOUR

          7 column : group the different calculation to have in the same field those differents results

          8 column : Sum(duration) , format hours will sum all the value in hours

          9 column : sum(duration) with interval function and this format 'DD / hh:mm' will convert the values in days and hours

           

          i Add somme sample value such as 8 h Office , 5 working days to test if the sum are wright and it seems

           

          Hope it's help

           

          Bruno

          • Re: how to convert values into duration?
            Vineeth Pujari

            try below,

             

            Change the number of working hrs per day as required, i've set to 8 Hrs

             

            Temp0:

            load *,interval(subfield(SLA,' ',1) * if(trim(Subfield(SLA,' ',3))='days',8,1)/24,'hh:mm') as SLANHrs Inline [SLA,

            2 working days,

            4 h Office,

            4 h Office,

            4 h Office,

            4 h Office,

            2 working days,

            3 working days,

            3 working days,

            5 working days,

            2 h Office,

            8 h Office,

            12 h Office,

            ];