topic Convert value from timestamp into int and increase value in QlikView

Convert value from timestamp into int and increase value

Thu, 19 Apr 2012 06:08:48 GMT

Goal:

Display value in hours as integer after completed calculation that is (answer = end - begin),

Problem:

After doing calculation you gain the result based on the sourcecode below. Some example of result is:

02:54

04:35

65:25

25:15

I need to make the value to become:

Current data Requested result

-----------------------------------------------------

02:54 > 03 or 3

04:35 > 05 or 5

65:29 > 65

25:15 > 25

123:32 > 124

The main problem is

I don't know how to do it based on the source code below.

If the number is higher than 29 the result will be increase with one value (12:30 wil be 13)

Begin	End
2011-11-10 12:12:12	2011-11-12 12:12:12
2011-11-10 12:12:12	2011-11-11 08:12:12

(The column Begin and End have timestamp as number format settings in doucment settings)

The source code is

INTERVAL

(

End - Begin

, 'hh:mm'

)

INTERVAL

(

End - Begin

, 'mm'

)

// JJ

Convert value from timestamp into int and increase value

Michiel_QV_Fan — Thu, 19 Apr 2012 07:30:51 GMT

If I understand the problem correctly than this might be a solution:

In the script:

(End-Begin)*24 as duration

then you get hours and parts of hours on a 1 to 100 scale

hours on 1 to 100 scale can be rounded off as your need

Convert value from timestamp into int and increase value

Thu, 19 Apr 2012 08:35:41 GMT

If I understand, the code is for script and would the code be the same if you use it in expressen?

Convert value from timestamp into int and increase value

Michiel_QV_Fan — Thu, 19 Apr 2012 09:13:05 GMT

Yes,

The code is designed for the script.

But it will work in the expression too: Sum (end -begin)*24

I suggest to add the code in the script, then your qlikview application has less work to do. It can calculate with the end result instead of calculating the end result and then display it.

In larger qlikview doing calculations in the script gives a performance enhancement.

Good luck

Re: Convert value from timestamp into int and increase value

Thu, 19 Apr 2012 17:06:11 GMT

Hi, try with this:

tmp:
LOAD * INLINE [
vTime
02:54
04:35
65:25
25:15
123:32
];
data:
load recno() as ID,
vTime as InitialTime,
if(mid(vTime,index(vTime,':')+1,2)>29,
mid(vTime,1,index(vTime,':')-1)+1,
mid(vTime,1,index(vTime,':')-1)) as FinalTime
resident tmp;
drop table tmp;

Regards