Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
markgraham123
Contributor II

Create a new field name which denotes the work start

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,EndNew Field

Paul,8/31/2015,Dual,8/31/15 11:45 PM,8/31/15 11:50 PM8/31/15
Paul,8/31/2015,Dual,8/31/15 11:50 PM,8/31/15 11:55 PM8/31/15
Paul,9/1/2015,Dual,9/1/15 12:00 AM,9/1/15 12:05 AM8/31/15
Paul,9/1/2015,Dual,9/1/15 12:05 AM,9/1/15 12:10 AM8/31/15
Paul,9/1/2015,Dual,9/1/15 12:10 AM,9/1/15 12:15 AM8/31/15
Paul,9/1/2015,Dual,9/1/15 12:15 AM,9/1/15 12:20 AM8/31/15

Please find attachment.

Any help is highly appreciated.

4 Replies

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

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;

markgraham123
Contributor II

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

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?

Untitled.png

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

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?

markgraham123
Contributor II

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

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)

Community Browser