Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
agrimroquette
Not applicable

how to convert values into duration?

yes,

sla.PNG

how to convert these values into duration?

1 Solution

Accepted Solutions
vinieme12
Not applicable

Re: how to convert values into duration?

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,

];

5 Replies
agrimroquette
Not applicable

Re: how to convert values into duration?

can any one help?

brunobertels
Not applicable

Re: how to convert values into duration?

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
Not applicable

Re: how to convert values into duration?

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,

];

agrimroquette
Not applicable

Re: how to convert values into duration?

thanks a lot Vineeth for help, its helpful

can you please see my new discussion ?

agrimroquette
Not applicable

Re: how to convert values into duration?

its really helpful thanks a lot bruno