convert seconds to hh:mm:ss in script

[#1]

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

[sunny_talwar]

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

[sunny_talwar]

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

[#4]

Awesome! Thanks!

[#5]

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!

[maxgro]

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.

[sunny_talwar]

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

[MarcoWedel]

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

[hic]

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