8 Replies Latest reply: Nov 15, 2015 8:56 AM by daniesh shaikh RSS

    add two fields

    daniesh shaikh

      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,

        • Re: add two fields
          Stefan Wühl

          LOAD

               IN_TIME,

               OUT_TIME,

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

          FROM YourDataSource;

          • Re: add two fields
            balraj ahlawat

            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

            • Re: add two fields
              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

                • Re: add two fields
                  daniesh shaikh

                  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

                    • Re: add two fields
                      daniesh shaikh

                      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
                          Anand Chouhan

                          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

                            • Re: add two fields
                              daniesh shaikh

                              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

                            • Re: add two fields
                              Stefan Wühl

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