Need to extract mins and secs from the text field

[Anonymous]

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

[adamdavi3s]

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]

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]

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

];

[Anonymous]

Hi,

Please see attached qvw to get the following fields:

[Anonymous]

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]

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

[Anonymous]

Hi,

Same logic but neater code version attached in _v2.

Hope it helps!

[Anonymous]

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