3 Replies Latest reply: Feb 13, 2015 11:15 AM by Frank Hartmann RSS

    How to modelize ?

    Laura Sébille

      Hello everyone,

       

      I currently have 2 tables, the first one contains data about the employees and their beginning/ end of shift and about their break.

      Table Employees:

       

      Employee           Event              Event time

      11111                  Begin shift       10/10/2014  08:00:00

      11111                  Begin break     10/10/2014  12:30:00

      11111                End break        10/10/2014  13:05:00

      11111                  End shift           10/10/2014  18:00:00

       

      And this for all employees.

       

      Now, some employee work on repairing cars, they have to go the place where the car had the problem. Each car incident has a number. In the database, when the employee affected to the incident leaves to get to the place, the status (event) turns to "On the way", when the employee is there, the status turns to "Arrived", and when the car is repaired (or the employee did all he had to do), the status goes to "finished". 

       

      Employee            Incident            Status              Event time

      11111                 AAAA1            On the way     06/10/2014    08:12:00

      11111                 AAAA1             Arrived            06/10/2014    08:37:00

      11111                 AAAA1             Finished          06/10/2014    09:27:00

      11111                 BBBB2            On the way     06/10/2014    09:30:00


      And so on...



      In my qlikview, I would like to get a table in which I can follow all the activites of the employees during each shift:


      Employee            Incident            Status              Event time

      11111                                           Begin shift       10/10/2014  08:00:00

      11111                 AAAA1            On the way     06/10/2014    08:12:00

      11111                 AAAA1             Arrived            06/10/2014    08:37:00

      11111                 AAAA1             Finished          06/10/2014    09:27:00

      11111                 BBBB2            On the way     06/10/2014    09:30:00

       

       

       

      Do you know how I could do ?

       

      Thank you for your help

        • Re: How to modelize ?
          Prashant Sangle

          Hi,

           

          Use Outer Join

           

          Try like

           

          Load Employee,

          Event as Status,

          Event Time

          From Employee;

           

          Join

          Load Employee,

          Incident,

          Status,

          Event Time

          From Table2;

           

          Regards

          • Re: How to modelize ?
            Srikanth P

            Simply concatenate those 2 tables like below:

             

            TABLE:

            LOAD

                Employee,

                Event AS Status,

                Event time

            From Employees;

            Concatenate ( TABLE )

            LOAD

                Employee,

                Incident,

                Status,

                Event time

            From Incidents;

            • Re: How to modelize ?
              Frank Hartmann

              Try this and set your systemtime back to 10/10/2014 08:00:00:

               

              employee:

              LOAD * Inline [

              Employee,          Event,            Eventtime

              11111,             Begin shift,       10/10/2014 08:00:00

              11111,             Begin break,       10/10/2014 12:30:00

              11111,             End break,         10/10/2014 13:05:00

              11111,             End shift,         10/10/2014 18:00:00

              ];

               

              temp1:

              LOAD

              Employee,

              Event as Status,

              Eventtime

              Resident employee;

              DROP Table employee;

               

              concatenate

               

              LOAD  * Inline [

              Employee,           Incident,         Status,             Eventtime

              11111,              AAAA1,            On the way,         06/10/2014 08:12:00

              11111,              AAAA1,            Arrived,            06/10/2014 08:37:00

              11111,              AAAA1,            Finished,           06/10/2014 09:27:00

              11111,              BBBB2,            On the way,         06/10/2014 09:30:00

              ];

               

              temp:

              NoConcatenate

              load

              Employee,

              Status,

              text(Incident) as Incident,

              if (Eventtime<=Time(Now()),Eventtime,NULL()) as Eventtime

              Resident temp1;

              DROP Table temp1;

               

               

              cheers!