Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

add two fields

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,

8 Replies
MVP
MVP

Re: add two fields

LOAD

     IN_TIME,

     OUT_TIME,

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

FROM YourDataSource;

balrajahlawat
Esteemed Contributor

Re: add two fields

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

mark6505
Valued Contributor III

Re: add two fields

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

Not applicable

Re: add two fields

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:MMSmiley FrustratedS

I will use the below expression and confirm.

if(OUT_TIME-IN_TIME>=8,'8 Hrs Completed','8 hrs not completed') as FieldName


Thanks

Not applicable

Re: add two fields

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

Re: add two fields

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

MVP
MVP

Re: add two fields

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

Not applicable

Re: add two fields

Hi

I tried the solution but i am getting value two times one time without flag and one with flag.

06:57:00.000014:17:00.000018/10/2015 00:00:0070:00:00
06:57:00.000014:17:00.000018/10/2015 00:00:0077:20:00Not Completed
06:59:00.000014:36:00.000011/10/2015 00:00:0070:00:00
06:59:00.000014:36:00.000011/10/2015 00:00:0077: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