Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

gauravgg
New Contributor III

convert to time ?

Hi , i have the following field in to HH:MMSmiley FrustratedS

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

Tags (2)
11 Replies
gauravgg
New Contributor III

Re: convert to time ?

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

Vegar
Valued Contributor

Re: convert to time ?

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')

sureshqv
Esteemed Contributor III

Re: convert to time ?

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;

kalyanivintha1
Contributor II

Re: convert to time ?

I think this will work

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

gauravgg
New Contributor III

Re: convert to time ?

thanks , this works

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

gauravgg
New Contributor III

Re: convert to time ?

hi

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

this is working but the negative values are showing positive

Vegar
Valued Contributor

Re: convert to time ?

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
New Contributor III

Re: convert to time ?

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)

MVP
MVP

Re: convert to time ?

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?

Community Browser