topic Re: convert seconds to hh:mm:ss in script in QlikView

convert seconds to hh:mm:ss in script

Thu, 25 Jun 2015 19:19:42 GMT

I have a field from SQL that is the number of seconds worked. I would like to convert this to a format of hh:mm:ss and still be able to do aggregations of the data.

In SQL I would use code like:

// RIGHT('0' + CAST("time_in_seconds" / 3600 AS VARCHAR),2) + ':' +

// RIGHT('0' + CAST(("time_in_seconds" / 60) % 60 AS VARCHAR),2) + ':' +

// RIGHT('0' + CAST("time_in_seconds" % 60 AS VARCHAR),2)

QlikView errors when I put this code in the Select statement.

I've seen some ways to format seconds on the front end, which I may end up going with, but I would rather the data be formated appropriately to begin with, so I don't take any extra memory to accomplish this tranformation, especially, since I will need that memory to handle summing the number of seconds and minutes.

I would greatly appreciate any assistance!

Greg

Re: convert seconds to hh:mm:ss in script

sunny_talwar — Thu, 25 Jun 2015 19:24:40 GMT

Try this:

Interval(Interval#(time_in_seconds, 'ss'), 'hh:mm:ss') as Time

Re: convert seconds to hh:mm:ss in script

maxgro — Thu, 25 Jun 2015 19:27:37 GMT

load *, seconds / (24*60*60) as newfield;

load * inline [

seconds

3600

1800

7200

7800

];

then format newfield in number tab

Re: convert seconds to hh:mm:ss in script

sunny_talwar — Thu, 25 Jun 2015 19:32:20 GMT

Massimo Grossi‌ is there a performance benefit of using your method over this?

load *, Interval(Interval#(seconds, 'ss'), 'hh:mm:ss') as Time;

load * inline [

seconds

3600

1800

7200

7800

];

Output is the same

Just out of curiosity

Re: convert seconds to hh:mm:ss in script

Thu, 25 Jun 2015 19:51:59 GMT

Thank you both! The first example worked perfectly!

This is very similar to converting it in the front end. I'm still learning how to apply front end script versus back end script.

If there is a performance gain with either option, I would love to know.

Thanks again!

Re: convert seconds to hh:mm:ss in script

maxgro — Thu, 25 Jun 2015 20:01:37 GMT

I tried a load, this is the result

t: load rowno() as id AutoGenerate 10000000;

a11:

load Interval(Interval#(rowno(), 'ss'), 'hh:mm:ss') as Time

resident t;

store a11 into a11.qvd (qvd);

a12:

load id / (24*60*60) as Time2

resident t;

store a12 into a12.qvd (qvd);

25/06/2015 21:56:17: 0013 t:

25/06/2015 21:56:17: 0014 load rowno() as id AutoGenerate 10000000

25/06/2015 21:56:17: 1 fields found: id, 10.000.000 lines fetched

25/06/2015 21:56:26: 0017 a11:

25/06/2015 21:56:26: 0018 load Interval(Interval#(rowno(), 'ss'), 'hh:mm:ss') as Time

25/06/2015 21:56:26: 0019 resident t

25/06/2015 21:56:26: 1 fields found: Time, 10.000.000 lines fetched

25/06/2015 21:57:35: 0020 store a11 into a11.qvd (qvd)

25/06/2015 21:57:38: 0023 a12:

25/06/2015 21:57:38: 0024 load id / (24*60*60) as Time2

25/06/2015 21:57:38: 0025 resident t

25/06/2015 21:57:38: 1 fields found: Time2, 10.000.000 lines fetched

25/06/2015 21:57:44: 0026 store a12 into a12.qvd (qvd)

Re: convert seconds to hh:mm:ss in script

sunny_talwar — Thu, 25 Jun 2015 20:14:42 GMT

Thanks Massimo Grossi‌ for sharing the information, but there are few things I would like to share and get your feedback on:‌

1) I see the difference in size , but why is the formatting different?

May be the formatting is causing the size to be different?

2) And also I changed the script a little

t: load rowno() as id AutoGenerate 10000000;

a11:

load Interval(Interval#(rowno() id, 'ss'), 'hh:mm:ss') as Time

resident t;

store a11 into a11.qvd (qvd);

a12:

load id / (24*60*60) as Time2

resident t;

store a12 into a12.qvd (qvd);

and saw big jump in performance:

Let me know what you think?

Best,

Sunny

P.S. Thanks for sharing a method to do performance testing.

Re: convert seconds to hh:mm:ss in script

Thu, 25 Jun 2015 20:29:53 GMT

Sorry, remember I'm still fairly new to this. Would you be able to explain how you are doing the performance testing? I know we are a way off topic from the original question.

If I understand Massimo's post right, the interval option took from 21:56:26 to 21:57:35, where as the calculation went from 21:57:38 to 21:57:44, so basically the difference of 9 seconds to 6 seconds.

In sunindia's test the interval option too from 4:06:00 to 4:06:15 and the calculation took from 4:06:18 to 4:06:23, which would be the difference between 15 seconds and 5 seconds.

Am I understnading this correctly?

How did you create these files?

Thanks!

Re: convert seconds to hh:mm:ss in script

sunny_talwar — Thu, 25 Jun 2015 20:32:28 GMT

You are absolutely right. I actually did not do the calculations, but the numbers look fairly close time wise

Re: convert seconds to hh:mm:ss in script

Thu, 25 Jun 2015 20:34:30 GMT

So how did you actually do this test?

Re: convert seconds to hh:mm:ss in script

sunny_talwar — Thu, 25 Jun 2015 20:37:14 GMT

Create a qvd with the script maxgro‌ posted and enable logfile:

and run the report. It will create a logfile in the same folder as the application and will create two qvds: a11 and a12

Re: convert seconds to hh:mm:ss in script

sunny_talwar — Thu, 25 Jun 2015 20:38:57 GMT

If I were you I will go with Massimo's solution because he is master of QlikView and I am novice in front of him. I am just trying to expand my knowledge with bringing out things which might not even be right

Best,

Sunny

Re: convert seconds to hh:mm:ss in script

Thu, 25 Jun 2015 20:38:58 GMT

Awesome! Thanks!

Re: convert seconds to hh:mm:ss in script

Thu, 25 Jun 2015 20:42:31 GMT

Your solution worked!...That is a lot further than I would have gotten on my own.

Massimo's is a little faster though, so i will likely try to implement it.

I think I'm the novice here.

I really appreciate the help from both of you!

Re: convert seconds to hh:mm:ss in script

maxgro — Thu, 25 Jun 2015 20:44:33 GMT

2) And also I changed the script a little

t: load rowno() as id AutoGenerate 10000000;

a11:

load Interval(Interval#(rowno() id, 'ss'), 'hh:mm:ss') as Time

resident t;

store a11 into a11.qvd (qvd);

yes, id is correct, I forgot to change rowno() to id in a11 load

1) I see the difference in size , but why is the formatting different?

May be the formatting is causing the size to be different?

Don't know why. Maybe rwunderlich‌ or hic‌ can explain.

I noticed the same thing for date (number / text).

It seems the store to qvd depends on the format (in .qvw, before the store).

And yes, mee too I think the qvd size is different because of formatting.

I suppose the same is true when there are 2 associated tables by a data field: number is better than text.

Re: convert seconds to hh:mm:ss in script

sunny_talwar — Thu, 25 Jun 2015 20:44:53 GMT

No problem at all

BTW this is the same way I learned from the community, I am sure you won't remain novice for too long

Re: convert seconds to hh:mm:ss in script

MarcoWedel — Thu, 25 Jun 2015 23:49:33 GMT

Hi,

interesting thread.

I did some testing building on maxgro‌ and sunindia‌‌ code too:

t: load rowno() as id AutoGenerate 10000000;

LET vStartTime = num(now());
a11:
LOAD id, Interval(Interval#(id, 'ss'), 'hh:mm:ss') as Time
Resident t;
LET vTraceMessage = 'load time table a11: '&Interval(now()-$(vStartTime));
TRACE $(vTraceMessage);
STORE a11 into a11.qvd (qvd);

LET vStartTime = num(now());
a12:
LOAD id, id/86400  as Time2
Resident t;
LET vTraceMessage = 'load time table a12 '&Interval(now()-$(vStartTime));
TRACE $(vTraceMessage);
STORE a12 into a12.qvd (qvd);

LET vStartTime = num(now());
a13:
LOAD id, Interval(id/86400, 'hh:mm:ss') as Time3
Resident t;
LET vTraceMessage = 'load time table a13 '&Interval(now()-$(vStartTime));
TRACE $(vTraceMessage);
STORE a13 into a13.qvd (qvd);

LET vStartTime = num(now());
a14:
LOAD id, Interval(Round(id/86400,'00:00:01'), 'hh:mm:ss') as Time4
Resident t;
LET vTraceMessage = 'load time table a14 '&Interval(now()-$(vStartTime));
TRACE $(vTraceMessage);
STORE a14 into a14.qvd (qvd);

It seems like the result are not numerical equal though:

So it's probably best to stick to the QlikView time-functions (even if the script runtime is longer) instead of doing some time arithmetic like suggested here

Correct Time Arithmetic | Qlikview Cookbook by rwunderlich‌.

hope this helps

regards

Marco

Re: convert seconds to hh:mm:ss in script

hic — Fri, 26 Jun 2015 07:14:37 GMT

Both methods are good. And the difference between the two is so small so I wouldn't bother about optimizing it. Remember: Premature optimization is the root of all evil!

But when you compare the two you need to make a fair comparison. For some reason, both Massimo and Sunny used a formatting function in a11 but not in a12. Of course you need to format both. The correct script for a12 should be:

a12:

load Interval( id / (24*60*60), 'hh:mm:ss') as Time2

resident t;

store a12 into a12.qvd (qvd);

But even here, a12 is faster. The reason is most likely that the interpretation function involves string comparisons, which are slower than pure numeric operations.

Finally, the small time differences that Marco has discovered are probably due to a small rounding which was done in time functions. (I think this has been removed in recent versions.)

HIC