topic Re: Need to extract mins and secs from the text field in QlikView

Need to extract mins and secs from the text field

Anonymous — Thu, 08 Dec 2016 09:00:20 GMT

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

Re: Need to extract mins and secs from the text field

adamdavi3s — Thu, 08 Dec 2016 09:20:03 GMT

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.

Re: Need to extract mins and secs from the text field

Anonymous — Thu, 08 Dec 2016 09:45:05 GMT

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..

Re: Need to extract mins and secs from the text field

adamdavi3s — Thu, 08 Dec 2016 09:52:53 GMT

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

Re: Need to extract mins and secs from the text field

sunny_talwar — Thu, 08 Dec 2016 10:14:36 GMT

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

];

Re: Need to extract mins and secs from the text field

Anonymous — Thu, 08 Dec 2016 10:26:58 GMT

Hi,

Please see attached qvw to get the following fields:

Re: Need to extract mins and secs from the text field

Anonymous — Thu, 08 Dec 2016 10:28:09 GMT

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?

Re: Need to extract mins and secs from the text field

adamdavi3s — Thu, 08 Dec 2016 10:30:05 GMT

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

Re: Need to extract mins and secs from the text field

Anonymous — Thu, 08 Dec 2016 10:40:53 GMT

Hi,

Same logic but neater code version attached in _v2.

Hope it helps!

Re: Need to extract mins and secs from the text field

Anonymous — Thu, 08 Dec 2016 10:41:50 GMT

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

Re: Need to extract mins and secs from the text field

sunny_talwar — Thu, 08 Dec 2016 10:46:17 GMT

Set your time format like this:

SET TimeFormat='h:mm:ss';

or change the script like this:

Re: Need to extract mins and secs from the text field

Anonymous — Thu, 08 Dec 2016 10:55:05 GMT

it worked finally..when i replaced interval# with timestamp# it worked but when i replaced interval with timestamp it didnt? will you be able to explain why you used interval?

Re: Need to extract mins and secs from the text field

sunny_talwar — Thu, 08 Dec 2016 11:00:05 GMT

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

Re: Need to extract mins and secs from the text field

sunny_talwar — Thu, 08 Dec 2016 12:08:17 GMT

Hi Sanjyot -

I am glad that you follow the 'unenforced' rule of marking correct and helpful response, but I would suggest you to reconsider what you have marked as correct and/or helpful. The response that actually helped you reach your final goal has neither been selected as correct or helpful. Lets just consider that you are looking for something and land up on a page like this what would be the first think you check? Correct response or helpful response? Guess what, in this thread you will have to make more effort than that?

What I am trying to suggest that mark correct and helpful responses not because you have an obligation to your responders and you want to give them additional points. Mark them because you have an obligation towards the community. Mark them because these responses will help others when they land on this page for answers.

Thanks for your time reading above and I hope you will reconsider your choices.

Thanks,

Sunny