add two fields

[#1]

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,

[swuehl]

LOAD

     IN_TIME,

     OUT_TIME,

     IF( RANGESUM(IN_TIME, OUT_TIME) <8, 'not completed','completed') AS [OFFICE HOURS]

FROM YourDataSource;

[Anonymous]

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

[Mark_Little]

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

[#5]

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

[#6]

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

[its_anandrjs]

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

[swuehl]

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$);

[#9]

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