topic Re: Time Calculation Help in QlikView

Time Calculation Help

amit_saini — Fri, 24 Nov 2017 04:19:05 GMT

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,

Re: Time Calculation Help

amit_saini — Fri, 24 Nov 2017 05:13:41 GMT

Any suggestion???

Re: Time Calculation Help

tresB — Fri, 24 Nov 2017 05:23:01 GMT

What are your input and expected output?

Re: Time Calculation Help

jonathandienst — Fri, 24 Nov 2017 05:31:24 GMT

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)

Re: Time Calculation Help

amit_saini — Fri, 24 Nov 2017 08:27:35 GMT

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

Re: Time Calculation Help

avkeep01 — Fri, 24 Nov 2017 12:02:52 GMT

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

Re: Time Calculation Help

MarcoWedel — Sat, 25 Nov 2017 19:39:44 GMT

Hi,

maybe you could use something like

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

to avoid Rounding Errors