topic Re: Convert h:mm;ss to seconds in QlikView

Convert h:mm;ss to seconds

Thu, 09 Jan 2014 13:17:45 GMT

Hi,

What is the easiest way to convert "1.30;00" to seconds?

The values after ";" that I guess is seconds is allways '00' so maybe it doesn't need to be taken care of.

Thanks in advance!

Re: Convert h:mm;ss to seconds

sujeetsingh — Thu, 09 Jan 2014 13:25:16 GMT

try this

interval(Interval#('02:30','mm:ss'),'ss')

Re: Convert h:mm;ss to seconds

luciancotea — Thu, 09 Jan 2014 13:29:06 GMT

=num( Time#('1.30;00', 'h.mm;ss')) * 24 * 3600

Re: Convert h:mm;ss to seconds

Thu, 09 Jan 2014 13:36:47 GMT

If I would like to use a field (that have the time format h.mm;ss) instead:

=num( Time#(field, 'h.mm;ss')) * 24 * 3600

Re: Convert h:mm;ss to seconds

Thu, 09 Jan 2014 13:37:47 GMT

And do it in the script?

Re: Convert h:mm;ss to seconds

Thu, 09 Jan 2014 13:48:52 GMT

try something like this:

Hej:

LOAD * INLINE [

Date,TimeStamp

'2014-01-02 01:30:23', '1.30;00'

];

tid:

Load

num( Time#(TimeStamp, 'h.mm;ss'))* 24 * 3600 as TimeField

Resident Hej;

Re: Convert h:mm;ss to seconds

luciancotea — Thu, 09 Jan 2014 16:08:11 GMT

You can do anything in script as long as you know that:

-DateTime field is in fact a FLOAT number where:

- the integer part = floor(DateTime) = Date info (number of days passed since 31 Dec 1899),

- the fractional part = frac(DateTime)) = Time info (time passed since midnight)

So, in our case, Time#('1.30;00', 'h.mm;ss') is converting from TEXT to TIME, using a custom format. Because you had only time info in your example I directly applied the num() function which transformed from TIME to FLOAT.

At the end I multiplied with 24 (hours) * 60 minutes * 60 seconds to transform from FLOAT to SECONDS.

Re: Convert h:mm;ss to seconds

Fri, 10 Jan 2014 08:36:44 GMT

Table:

Id,

Amount,

OBE,

OBK,

OBS,

num(Time#(OBE, 'h.mm;ss'))*24*3600 as OBE_Time,

num(Time#(OBK, 'h.mm;ss'))*24*3600 as OBK_Time,

num(Time#(OBS, 'h.mm;ss'))*24*3600 as OBS_Time

FROM ...;

Give me for example;

59,99999893892

120,0000003916

360,0000117347

That i guess is the right number of seconds.

Thanks!

Re: Convert h:mm;ss to seconds

Anonymous — Fri, 10 Jan 2014 08:47:59 GMT

use something like this

evaluate ( subfield(Yourfield,'.',1) *60*60 + subfield(Yourfield,';',2) * 60 + subfield(Yourfield,';',3) )

anant