Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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