Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Any suggestion???
What are your input and expected output?
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)
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
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
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