Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
.
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,
];
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
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,
];