topic Problem with hours and minutes in QlikView

Problem with hours and minutes

Thu, 25 Feb 2016 12:30:54 GMT

Hello to all,

In the load script, attached example, I have to calculate the values of days,

hours and minutes from a field (h1) in hh,mm format.

For example, 204.45 are 204 hours and 45 minutes.

I turn around in minutes (see field min20) and after turn into days with the div function and create the g20 field. One business day corresponds to 455 minutes. In my example should give 12285/455 27 whereas in QLV get 26.

Why is this happening?

Re: Problem with hours and minutes

sunny_talwar — Thu, 25 Feb 2016 12:45:51 GMT

Try this may be:

T2:

Load *

,floor(H1) as h11

,num(frac(H1)*100,'###') as min11

,((floor(H1) * 60) + num(frac(H1)*100,'###') ) as min20

,Round(((floor(H1) * 60) + (frac(H1)*100) )/ 455) as g20

resident T1;

Drop table T1;

The issue is that your number display 12285, but it is in frac part is slightly less than 45

You can also consider using Ceil() function instead of Round() function

Re: Problem with hours and minutes

hic — Thu, 25 Feb 2016 12:50:07 GMT

Why don't you use the built-in time functions?

E.g.

Minute(Timestamp#(H1,'hh.mm')) as Min,

Interval(Timestamp#(H1,'hh.mm')) as Hour,

Floor(24*(Timestamp#(H1,'hh.mm'))) as Hours,

etc.

See The Date Function for more on interpretation functions. The same is applicable for Timestamp#(), Time#(), etc.

HIC