Time Calculation Help

[amit_saini]

Hi Folks,

In the script side , I'm using below logic to match time 21:59:59

num(frac(time(Date.tmp)),'0,0000000')-0.0000001 as Time, // without subtraction of 0.0000001 the time 21:59:59 was not matched

What shall I should minus from below to get 20:45:00????

num(frac(time(Date.tmp)),'0,0000000') - XXXXXXX ?

Please help!

Regards,

AS

[amit_saini]

Any suggestion???

[tresesco]

What are your input and expected output?

[jonathandienst]

You requirement is not clear.

>>What shall I should minus from below to get 20:45:00????

>> num(frac(time(Date.tmp)),'0,0000000') - XXXXXXX ?

Well it depends on the value of Date,tmp. I am not sure what you are trying to do. To get the value of time 20:45, use

     =#Time('20:45', 'hh:mm')

     =MakeTime(20, 45)

[amit_saini]

Hello,

What I'm doing is based on Shift table as interval match is looking in CAQ table and taking distinct count of serial number:

My Night Shift numbers are 100% matching with data source with below logic

IF(hour(Date.tmp2) < 8.45, Date.tmp2-1,Date.tmp2) as Date.tmp

But number are not matching for Day shift.

Shift Table :

Script:

Shift:

LOAD Shift_Id,

     Shift_Name,

     num(Start_Hour) as Start,

     num(End_Hour - Time#('00:00:01'),'0,0000000') as End,

     Line_Id,

     Facility_Id

FROM

[$(vDataPath)Shift.qvd]

(qvd);

//exit script;

Shifts:

//Shift_Early_Late:

NoConcatenate

LOAD

    *

Resident Shift

where End > Start;

Shift_Night:

NoConcatenate

LOAD

    *

Resident Shift

where Start > End;

Shift_Night_1:

Concatenate (Shifts)

LOAD

    Shift_Id,

     Shift_Name,

     Start,

     1 as End,

     Line_Id,

     Facility_Id

Resident Shift_Night;

Shift_Night_2:

Concatenate (Shifts)

LOAD

    Shift_Id,

     Shift_Name,

     0 as Start,

     End,

     Line_Id,

     Facility_Id

Resident Shift_Night;

drop tables Shift, Shift_Night;

CAQ:

LOAD      

     date(floor(Date.tmp)) as Date,

     num(frac(time(Date.tmp)),'0,0000000')-0.0000001 as Time, // without substraction of 0.0000001 the time 21:59:59 was not matched?!

     year(Date.tmp) as Year,

     month(Date.tmp) as Month,

     week(Date.tmp) as Week,

     Day(Date.tmp) as Day,

     num(Date.tmp) as NumDate,

//dual(year(Date.tmp)&'-'&'Q'&ceil(month(Date.tmp)/3),year(Date.tmp)&ceil(month(Date.tmp)/3)) as Year_Quarter,

//    dual(year(Date.tmp)&'-'&month(Date.tmp),year(Date.tmp)&num(month(Date.tmp),'00')) as Year_Month,

//    dual(year(Date.tmp)&'-'&week(Date.tmp),year(Date.tmp)&num(week(Date.tmp),'00')) as Year_Week,

     *

     ;

LOAD

    *,

    hour(Date.tmp2) as Time.tmp,

    IF(hour(Date.tmp2) < 8.45, Date.tmp2-1,Date.tmp2) as Date.tmp  // Logic for night shift

    ;

LOAD

    num(Date) as Date.tmp2,

    timestamp(Date) as Date_Org,

     Facility_Id,

     Product,

     Variant,

     Station,

     Serial,

     Characteristic,

     Values,

     Status,

     DTTSERF

FROM

[$(vDataPath)CAQ_table.qvd]

(qvd);

left join (CAQ)

IntervalMatch (Time,Facility_Id)

Load distinct

    Start,

    End,

    Facility_Id

Resident Shifts;

left join(CAQ)

LOAD distinct

    Facility_Id,

    Start,

    End,

    Line_Id,

    Shift_Name

Resident Shifts;

drop table Shifts;

Thanks,

Amit

[avkeep01]

Hi Amit,

Not quite sure if I'm getting the question right but it seems to me that you can use a FLOOR statement in your script

When you use time or datetime it only displays the number in another format it doesn't drop the decimal values after the seconds.

DATETIME(FLOOR(Date.tmp *24*60*60)/ (24*60*60))

Floor is a function which round the values to the first lower integers, so 5.7 = 5.

A date is an integer, while the hours, minutes and seconds are decimal values. Multiplying with 24 makes the hours integers, when adding a multiply of 60 the minutes are integers etc.

So floor(date.tmp*24*60*60) / (24*60*60) rounds your date to seconds

[MarcoWedel]

Hi,

maybe you could use something like

Time#(Time(Frac(Date.tmp),'hh:mm:ss'),'hh:mm:ss')

to avoid Rounding Errors

See also:

Correct Time Arithmetic | Qlikview Cookbook  by Rob Wunderlich

convert seconds to hh:mm:ss in script

Reference DateTime for calculating occupancy

hope this helps

regards

Marco