convert to time ?

[gauravgg]

Hi , i have the following field in to HH:MM:SS

example:---0 days 0Hr 0Min 14Sec into 00:00:14

0 days 0Hr 0Min 0Sec ->00:00:00

0 days 0Hr 0Min 36Sec->00:00:36

0 days 0Hr 0Min 0Sec

0 days 0Hr 0Min 19Sec

0 days 0Hr 1Min 42Sec

0 days 0Hr 2Min 14Sec

0 days 0Hr 0Min 29Sec

0 days 0Hr 1Min 45Sec

0 days 0Hr 0Min 0Sec

Thanks in Advance

[gauravgg]

i have to convert       0 days 0Hr 0Min 36Sec  to   00:00:36

[Vegar]

I think the following will work

Interval(Interval#(Keepchar(yourField, '0123456789 '), 'D h m s'), 'hh:mm:ss')

Or this  with double spaces between D and h

Interval(Interval#(Keepchar(yourField, '0123456789 '), 'D  h m s'), 'hh:mm:ss')

[Chanty4u]

Am not sure, Not tested.  but try this

a:

LOAD * INLINE [

    Hour

    0 days 0Hr 0Min 0Sec

    0 days 0Hr 0Min 19Sec

    0 days 0Hr 1Min 42Sec

    0 days 0Hr 2Min 14Sec

    0 days 0Hr 0Min 29Sec

    0 days 0Hr 1Min 45Sec

    0 days 0Hr 0Min 0Se

]

Result;

LOAD * ,

time(timestamp#(Hour,'0 days' h '0hr' m '0Min' s '0sec'),'hhmmss') as new

Resident a;

[Anonymous]

I think this will work

Trim(KeepChar(Replace(Replace(Replace(your filedname,'0 days',''),'Hr',':'),'Min',':'),'01234567890:'))

[gauravgg]

thanks , this works

Interval(Interval#(Keepchar(yourField, '0123456789 '), 'D  h m s'), 'hh:mm:ss')

[gauravgg]

hi

Interval(Interval#(Keepchar(yourField, '0123456789 '), 'D  h m s'), 'hh:mm:ss')

this is working but the negative values are showing positive

[Vegar]

Keepchar()  removes all characters in your string except the ones in the second parameter.

If your negative numbers at annotated with minus, eg -2 , then you should add the - char to this parameter like this

Interval(Interval#(Keepchar(yourField, '-0123456789 '), 'D  h m s'), 'hh:mm:ss')

[gauravgg]

if i apply this

Interval(Interval#(Keepchar(yourField, '-0123456789 '), 'D  h m s'), 'hh:mm:ss')

0days 0Hrs 0Min -1Sec --> it is converting to -00:00:29 .......(it should show -00:00:01)

[jonathandienst]

The problem here is that the minus sign lands up in the middle of the interval expression and it looks like QV cannot handle that. This works correctly:

=Interval(Interval#(Keepchar('-0days 0Hrs 0Min 1Sec', '-0123456789 '), 'D  h m s'), 'hh:mm:ss')

Can the sign appear on any of the quantities in this time string?