topic Re: Re: how to make this population? in QlikView

how to make this population?

rcandeo — Mon, 18 Nov 2013 15:41:50 GMT

Hi, I have a table that has information of the employees from an industry and they have to register each time they started working and they work for 6 hours each time. This rotation is registered as a number this way:

rotation 1 begins at 07:00

rotation 2 begins at 13:00

rotation 3 begins at 19:00

rotation 4 begins at 01:00

The employees work for projects that has date/time of the beginning and the ending.

Each employee registers for a specific equipment.

As some employes has delays or even forget to register sometimes, I have to control if the employees have registered correctly.

My input table is the project master data that contains the project number, the equipments used, the beginning date/time and the ending date/time.

My result table must contain the project number, the equipment and all the records of each rotation to compare with the employees registers.See that I need only the rotation between the initial and ending date.

For example If I have as input, this table:

Project Equipment Begining Date/Time Ending Date/Time

Project1 Eqp01 25/11/2013 10:12:08 26/11/2013 14:07:50

Project1 Eqp02 25/11/2013 10:15:32 26/11/2013 19:09:45

My result table must be:

Project Equipment Rotation Date

Project1 Eqp01 2 25/11/2013 13:00:00

Project1 Eqp01 3 25/11/2013 19:00:00

Project1 Eqp01 4 26/11/2013 01:00:00

Project1 Eqp01 1 26/11/2013 07:00:00

Project1 Eqp01 2 26/11/2013 13:00:00

Project1 Eqp02 2 25/11/2013 13:00:00

Project1 Eqp02 3 25/11/2013 19:00:00

Project1 Eqp02 4 26/11/2013 01:00:00

Project1 Eqp02 1 26/11/2013 07:00:00

Project1 Eqp02 2 26/11/2013 13:00:00

Project1 Eqp02 3 26/11/2013 19:00:00

Which is the best way to do this please?

Re: how to make this population?

Anonymous — Mon, 18 Nov 2013 18:45:42 GMT

Ronson

How does the attached bb3.qvw look like to you ?

Do read the IntervalMatch blog of hic it explains the IntervalMatch() the qvw depends on.

Best Regards, Bill

Re: how to make this population?

rcandeo — Mon, 18 Nov 2013 19:26:52 GMT

Hi, thank you for your attention.

You fixed the Rotation Date and I need to put this in a flexible way so any project has a diferent beginning date/time.

If you can help me this too, ll appreciate.

Use this please:

ProjectEquipment:

LOAD * INLINE [

Project, Equipment , Begining, Ending

Project1, Eqp01, 25/11/2013 10:12:08, 26/11/2013 14:07:50

Project1, Eqp02, 25/11/2013 10:15:32, 26/11/2013 19:09:45

Project2, Eqp01, 27/11/2013 08:10:00, 28/11/2013 20:00:40

];

Re: Re: how to make this population?

Anonymous — Mon, 18 Nov 2013 19:50:28 GMT

Robson

Just adding in the extra date here handles your extra data line

LOAD * INLINE [

RotationDate

25112013

26112013

27112013

];

For flexibility you'll need to find the min & max dates from your data loaded and generate a calendar that covers your required dates.

I'd suggest you close this thread and ask a new question if need help with that. Feel free to attach my qvw to it.

Best Regards, Bill

Re: how to make this population?

rcandeo — Tue, 19 Nov 2013 00:26:00 GMT

Hi, I did my lesson and maked it flexible creating a calendar with the beginning and ending date.

Thank You very much. I wouldn´t get it without your help.