topic Re: Create a new field name which denotes the work start in QlikView

Create a new field name which denotes the work start

markgraham123 — Tue, 10 May 2016 13:52:48 GMT

Hi all,

I'm trying to get a new date field from my existing data set which shows the original work start time (where work ends next day).

Eg:

I have an emp. who works from 9/1/2015 11:30 PM to 9/2/2015 6:00 am. Data has every 5 min. tracking

Emp,	Starting,	Work Mode,	Start,	End
Paul,	8/31/2015,	Dual,	8/31/15 11:45 PM,	8/31/15 11:50 PM
Paul,	8/31/2015,	Dual,	8/31/15 11:50 PM,	8/31/15 11:55 PM
Paul,	9/1/2015,	Dual,	9/1/15 12:00 AM,	9/1/15 12:05 AM
Paul,	9/1/2015,	Dual,	9/1/15 12:05 AM,	9/1/15 12:10 AM
Paul,	9/1/2015,	Dual,	9/1/15 12:10 AM,	9/1/15 12:15 AM
Paul,	9/1/2015,	Dual,	9/1/15 12:15 AM,	9/1/15 12:20 AM

At the end of the work at 12:20 am, the start column shows as 9/1/15.

I'm trying to create new filed which shows like below:

Emp,	Starting,	Work Mode,	Start,	End	New Field
Paul,	8/31/2015,	Dual,	8/31/15 11:45 PM,	8/31/15 11:50 PM	8/31/15
Paul,	8/31/2015,	Dual,	8/31/15 11:50 PM,	8/31/15 11:55 PM	8/31/15
Paul,	9/1/2015,	Dual,	9/1/15 12:00 AM,	9/1/15 12:05 AM	8/31/15
Paul,	9/1/2015,	Dual,	9/1/15 12:05 AM,	9/1/15 12:10 AM	8/31/15
Paul,	9/1/2015,	Dual,	9/1/15 12:10 AM,	9/1/15 12:15 AM	8/31/15
Paul,	9/1/2015,	Dual,	9/1/15 12:15 AM,	9/1/15 12:20 AM	8/31/15

Please find attachment.

Any help is highly appreciated.

Re: Create a new field name which denotes the work start

sunny_talwar — Tue, 10 May 2016 13:57:03 GMT

May be like this Mark?

Table:

LOAD * Inline

[

Emp, Starting, Work Mode, Start, End,

Paul, 8/31/2015, Dual, 8/31/15 11:45 PM, 8/31/15 11:50 PM

Paul, 8/31/2015, Dual, 8/31/15 11:50 PM, 8/31/15 11:55 PM

Paul, 9/1/2015, Dual, 9/1/15 12:00 AM, 9/1/15 12:05 AM

Paul, 9/1/2015, Dual, 9/1/15 12:05 AM, 9/1/15 12:10 AM

Paul, 9/1/2015, Dual, 9/1/15 12:10 AM, 9/1/15 12:15 AM

Paul, 9/1/2015, Dual, 9/1/15 12:15 AM, 9/1/15 12:20 AM

Paul, 9/1/2015, Dual, 9/1/15 12:20 AM, 9/1/15 12:25 AM

Paul, 9/1/2015, Dual, 9/1/15 12:25 AM, 9/1/15 12:30 AM

Paul, 9/1/2015, Dual, 9/1/15 11:45 PM, 9/1/15 11:50 PM

Paul, 9/1/2015, Dual, 9/1/15 11:50 PM, 9/1/15 11:55 PM

Paul, 9/2/2015, Dual, 9/2/15 12:00 AM, 9/2/15 12:05 AM

Paul, 9/2/2015, Dual, 9/2/15 12:05 AM, 9/2/15 12:10 AM

Paul, 9/2/2015, Dual, 9/2/15 12:10 AM, 9/2/15 12:15 AM

];

Left Join (Table)

LOAD Emp,

Date(Min(Starting)) as NewField

Resident Table

Group By Emp;

Re: Create a new field name which denotes the work start

markgraham123 — Tue, 10 May 2016 14:06:37 GMT

Sunny, Thanks for reply!

But that takes the very first date on which Emp. started right>

See the O/P Below:

If emp. started his work couple of months back, it still shows very first date as we r grouping by Emp!! (In this case, it shows 8/31 even for the start date of 9/2

Any thoughts brother?

Re: Create a new field name which denotes the work start

sunny_talwar — Tue, 10 May 2016 14:10:09 GMT

So how would be know which work belongs to white start day. May be the 9/2/2015 work also belonged to the 8/31 work? Os it it always 2 days work?

Re: Create a new field name which denotes the work start

markgraham123 — Tue, 10 May 2016 14:15:46 GMT

Sunny,

Here is the info:

A shift starts at night of 8/31 and end at 9/1 early morning:

We can see that info. in the application.

The reason we need new filed is, when counting the emp. performance on 9/1, the start time begins from 9/1/2015 12:00 am.

So, we need a new filed to separate 8/31/2015 11:45 to 9/1/2015 12:25 am into '8/31/2015 night shift'

Next shift started at 9/1/2015 11:50 pm.(as per above application and screenshot)