How to modelize ?

[#1]

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

[PrashantSangle]

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

[#3]

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;

[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!