Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Need to extract mins and secs from the text field

I need to extract mins and secs from a field that is in text format

My field name is [Resolution time] and the values are pasted below . I have used the below to extract hours trim(left([BPS Response Duration],index([BPS Response Duration],' hours')))  as Hrs, but finding it difficult mins and secs. Can anybody please help how do i go about extract mins and secs..

0 hours

3 mins 48 secs

4 secs

6 secs

8 secs

9 secs

10 hours 30 mins

10 secs

11 secs

13 secs

14 secs

15 secs

18 secs

21 secs

25 secs

28 hours 45 secs

29 hours 23 mins 45 secs

34 mins 43 secs

43 hours 56 mins 10 secs

50 hours 54 mins

120 hours

1000 hours

2000 hours

48 mins

   

1 Solution

Accepted Solutions
sunny_talwar

TimeStamp is just it looks, it is a timestamp, whereas interval is used for duration. Your machine started at 12/08/2016 5 am and kept running for 52 hours and 20 mins. First is a timestamp and second is a duration (interval).

Does that make sense

View solution in original post

13 Replies
adamdavi3s
Master
Master

This is dirty but it will work:

trim(left([BPS Response Duration],index([BPS Response Duration],' hours')))  as Hrs,

if( len(trim([BPS Response Duration]))=6 AND right(trim([BPS Response Duration]),4)='secs', trim(mid([BPS Response Duration],index([BPS Response Duration],' secs')-1,1)) , trim(mid([BPS Response Duration],index([BPS Response Duration],' secs')-2,2)))  as Secs,

right(trim([BPS Response Duration]),4),

trim(mid([BPS Response Duration],index([BPS Response Duration],' mins')-2,2))  as Mins

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

Anonymous
Not applicable
Author

what is there on line 3 mean  i.e. -right(trim([BPS Response Duration]),4),  ? is this part of mins or secs script as I didnt find any label to it?

the mins script works only if there are 2 digits in mins but if there is a single digit in mins i.e 2 mins it doesnt work..

adamdavi3s
Master
Master

whoops sorry I pasted an old code, try this

trim(left([BPS Response Duration],index([BPS Response Duration],' hours')))  as Hrs,

if( len(trim([BPS Response Duration]))=6 AND right(trim([BPS Response Duration]),4)='secs',purgechar([BPS Response Duration],' secs') , trim(mid([BPS Response Duration],index([BPS Response Duration],' secs')-2,2)))  as Secs,

if( len(trim([BPS Response Duration]))=6 AND right(trim([BPS Response Duration]),4)='mins',purgechar([BPS Response Duration],' mins') , trim(mid([BPS Response Duration],index([BPS Response Duration],' mins')-2,2)))  as Mins

sunny_talwar

Another method:

MappingLoad:

Mapping

LOAD * Inline [

Old, New

HOUR, a

HOURS, a

MINS, b

MIN, b

SECS, c

SECM, c

];

Table:

LOAD *,

  Interval(Alt(Num(Interval#(NewField, 'h a mm b ss c')), Num(Interval#(NewField, 'mm b ss c')), Num(Interval#(NewField, 'h a')), Num(Interval#(NewField, 'mm b')), Num(Interval#(NewField, 'ss c')), Num(Interval#(NewField, 'h a ss c')), Num(Interval#(NewField, 'h a mm b'))))  as TimeStamp;

LOAD *,

  MapSubString('MappingLoad', Upper(Field)) as NewField;

LOAD * Inline [

Field

0 hours

3 mins 48 secs

4 secs

6 secs

8 secs

9 secs

10 hours 30 mins

10 secs

11 secs

13 secs

14 secs

15 secs

18 secs

21 secs

25 secs

28 hours 45 secs

29 hours 23 mins 45 secs

34 mins 43 secs

43 hours 56 mins 10 secs

50 hours 54 mins

120 hours

1000 hours

2000 hours

48 mins

];

Capture.PNG

Anonymous
Not applicable
Author

Hi,

Please see attached qvw to get the following fields:

StringManipulation.PNG

Anonymous
Not applicable
Author

Thanks it works but there is one issue..if i enter 3 mins 43 secs..mins are not calculated but secs are only shown. is it something to do with the len of the string?

adamdavi3s
Master
Master

Rats yes of course, sorry I hadn't considered that scenario.... sunny's answer will probably be your best bet!

Anonymous
Not applicable
Author

Hi,

Same logic but neater code version attached in _v2.

Hope it helps!

Anonymous
Not applicable
Author

Attaching the picture of timestamp..I dont need AM PM in the timestamp..it should just show 43:56:10