how to convert values into duration?

[xyz1]

.

[brunobertels]

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

[vinieme12 · Accepted solution]

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,

];