Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a table where it has data like name , IN_TIME and OUT_TIME.
I am calculating working hours for employees. Like the employees who will complete 8 hours and employees not completing 8 hours.
Suppose addiition of IN_TIME and OUT_TIME will have 8 which means that employees is completing office hours.
I want to write a expression IN_TIME + OUT_TIME = 8 then Employees is completing office hours.
and IN_TIME + OUT_TIME < 8 not completing office hours.
Please help me in writing the expression.
Thanks and Regards,
LOAD
IN_TIME,
OUT_TIME,
IF( RANGESUM(IN_TIME, OUT_TIME) <8, 'not completed','completed') AS [OFFICE HOURS]
FROM YourDataSource;
Make sure you are having same time format & then try at script level:
if(OUT_TIME-IN_TIME>=8,'8 Hrs Completed','8 hrs not completed') as FieldName
Hi,
As the guys above have suggested, but maybe if you could supply some sample data as for the suggestions to work you would need the in and out time on the same row.
if(OUT_TIME-IN_TIME>=8,'8 Hrs Completed','8 hrs not completed') as FieldName
Mark
Hi,
IN_TIME and OUT_TIME is on the same row.
Currently i am out of office. The Data is in 08:00:00.000 09:00:00
for IN_TIME = 08:00:00
and OUT_TIME =HH:MM:SS
I will use the below expression and confirm.
if(OUT_TIME-IN_TIME>=8,'8 Hrs Completed','8 hrs not completed') as FieldName
Thanks
I have attached the sample data.
I want to configure one-to-one mapping of IN_TIME and outtime to 8 hours completed and 8 hours not completed.
I am using straight table where in i am not getting for one-to-one mapping.
Thanks
Hi,
You can try this ways by creating flag field in the load script
Temp:
LOAD M_DATE,
IN_TIME,
OUT_TIME,
Interval( OUT_TIME - IN_TIME,'hh:mm:ss' ) as [Total Time],
Hour(Interval( OUT_TIME - IN_TIME,'hh' )) as [Total Hours]
FROM
C:\Users\Home\Downloads\IT_TIME.xls
(biff, embedded labels, table is [Sheet1$]);
Main:
LOAD
*,
If([Total Time] = '08:00:00' or [Total Hours] >= '8' ,'Completed', 'Not Completed') as Flag
Resident Temp;
DROP Table Temp;
Also check the attached file qlikview file for solution
Hope this helps you
Regards,
Anand
You'll need to decide how you want to handle the multiple rows with same M_DATE. I've created an additional RecID field to distinguish between the duplicate date values.
Then, since your OUT_TIME and IN_TIME are QV time values, i.e. fraction of the as number, you need to compare against the time interval of 8 hours, which corresponds to 8/24, i.e. 1/3, or '08:00:00'.
You also need to think about how you want to handle missing OUT_TIME. I've added two calculations, one considering the missing OUT_TIME as unkown, one assuming an OUT_TIME as midnight if missing.
LOAD *,
Interval(OUT_TIME - IN_TIME) as Interval1,
Interval(Alt(OUT_TIME,1) - IN_TIME) as Interval2,
if( (OUT_TIME - IN_TIME) > '08:00:00', 'more than 8 hours', 'not more than 8 hours or unknown OUT') as Status,
if( (Alt(OUT_TIME,1) - IN_TIME ) > '08:00:00', 'more than 8 hours, unkown OUT set to midnight', 'not more than 8 hours') as Status2,
(Alt(OUT_TIME,1) - IN_TIME ) as Test,
Alt(OUT_TIME,1) as Test2;
LOAD Recno() as RecID,
M_DATE,
Time#(IN_TIME) as IN_TIME,
Time#(OUT_TIME) as OUT_TIME
FROM
[IT_TIME.xls]
(biff, embedded labels, table is Sheet1$);
Hi
I tried the solution but i am getting value two times one time without flag and one with flag.
06:57:00.0000 | 14:17:00.0000 | 18/10/2015 00:00:00 | 7 | 0:00:00 | |
06:57:00.0000 | 14:17:00.0000 | 18/10/2015 00:00:00 | 7 | 7:20:00 | Not Completed |
06:59:00.0000 | 14:36:00.0000 | 11/10/2015 00:00:00 | 7 | 0:00:00 | |
06:59:00.0000 | 14:36:00.0000 | 11/10/2015 00:00:00 | 7 | 7:37:00 | Not Completed |
As per the above output the first two columns are same but it has displayed twice one with null flag and other as not completed
OP is same for all the users
I have attached the xls file for your reference.
Thanks