topic Re: How to modelize ? in QlikView

How to modelize ?

Fri, 13 Feb 2015 13:05:32 GMT

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 ?

PrashantSangle — Fri, 13 Feb 2015 13:15:59 GMT

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 ?

Fri, 13 Feb 2015 15:42:33 GMT

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 — Fri, 13 Feb 2015 16:10:58 GMT

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!