Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Convert value from timestamp into int and increase value

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)

BeginEnd
2011-11-10 12:12:122011-11-12 12:12:12
2011-11-10 12:12:122011-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'

)

or

INTERVAL

(

     End - Begin

     , 'mm'

)

// JJ

4 Replies
michielvandegoo
Valued Contributor

Convert value from timestamp into int and increase value

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

Not applicable

Convert value from timestamp into int and increase value

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

michielvandegoo
Valued Contributor

Convert value from timestamp into int and increase value

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

Not applicable

Re: Convert value from timestamp into int and increase value

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

Community Browser