topic Re: Problem in Getting Actual Duration in Mins in QlikView

Problem in Getting Actual Duration in Mins

umerikhlas — Tue, 20 Sep 2016 07:11:35 GMT

Dear Fellow Members,

Attached please find the excel sheet containing Shift Details and Employee Time In and Out Details.

Using interval match I was able to find the Duration In Mins an Employee worked for but it includes Break Time (In Mins)

Note: Shifts and break times are not fixed.

Any help would be appreciated.

Regards,

Re: Problem in Getting Actual Duration in Mins

Anil_Babu_Samineni — Tue, 20 Sep 2016 07:29:10 GMT

What are the issue?

Re: Problem in Getting Actual Duration in Mins

Gysbert_Wassenaar — Tue, 20 Sep 2016 07:29:25 GMT

See this discussion: Task/Ticket Duration

Re: Problem in Getting Actual Duration in Mins

effinty2112 — Tue, 20 Sep 2016 07:29:37 GMT

Hi Umer,

Try:

Interval(End -Start-BreakInMins/1440)

Kind regards

Andrew

Re: Problem in Getting Actual Duration in Mins

ajsjoshua — Tue, 20 Sep 2016 07:30:06 GMT

Hi,

check below

Breakdown time

Re: Problem in Getting Actual Duration in Mins

umerikhlas — Wed, 28 Sep 2016 06:18:22 GMT

The above mentioned solutions are not working in my case. Any further help would be appreciated as it is a mission critical requirement.

Re: Problem in Getting Actual Duration in Mins

adamdavi3s — Wed, 28 Sep 2016 06:23:08 GMT

Why are they not working?

How are you matching shifts to breaks?

Can you provide a sample qvd with your working in so we at least have something to go on?

Re: Problem in Getting Actual Duration in Mins

umerikhlas — Thu, 29 Sep 2016 12:56:46 GMT

Hi All Qlikers,

Did some work with the help of my colleagues.

Shift Details (with Start and End Times and Break In Minutes) shown in figure 1.

Employee Details (with Time In and Time Out) shown in figure 2.

Sum of Breaks can be found in figure 3 in field named as Test.

Now, Problem is that it works correctly only if the Shift Start Time is exact equal to (with seconds) as Employee Time In and Shift End Time is exact equal to (with seconds) as Employee Time Out, which would not be the case every time.

Any help would be appreciated.

Sample files attached.

Kind Regards,

Re: Problem in Getting Actual Duration in Mins

Anonymous — Thu, 29 Sep 2016 14:05:38 GMT

Hi Umer,

To load your data (script) :

Timing:

LOAD

EmpID,

EmpName,

Timestamp(TimeStamp#(TimeIn,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm') AS TimeIn,

TimeStamp(TimeStamp#(TimeOut,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm') AS TimeOut;

LOAD * INLINE [

EmpID, EmpName, TimeIn, TimeOut

1001, Yawar, 8/27/16 7:00, 8/27/16 14:15

1002, Jahanzaib, 8/27/16 9:10, 8/27/16 20:00

1003, Sehan, 8/27/16 7:00, 8/27/16 23:59

];

Times:

LOAD

Shift,

Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY') AS Date,

Timestamp(TimeStamp#(Start,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm') AS Start,

TimeStamp(TimeStamp#(End,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm') AS End,

BreakInMins;

LOAD * INLINE [

Shift, Date, Start, End, BreakInMins

1, 27/08/2016, 8/27/16 7:00, 8/27/16 9:00, 0

2, 27/08/2016, 8/27/16 9:10, 8/27/16 12:00, 10

3, 27/08/2016, 8/27/16 12:30, 8/27/16 14:15, 30

4, 27/08/2016, 8/27/16 14:25, 8/27/16 18:00, 10

5, 27/08/2016, 8/27/16 18:10, 8/27/16 20:00, 10

6, 27/08/2016, 8/27/16 20:30, 8/27/16 22:00, 30

7, 27/08/2016, 8/27/16 22:10, 8/27/16 23:59, 10

1, 28/08/2016, 8/28/16 7:00, 8/28/16 9:00, 0

2, 28/08/2016, 8/28/16 9:20, 8/28/16 12:10, 20

3, 28/08/2016, 8/28/16 12:45, 8/28/16 14:30, 35

4, 28/08/2016, 8/28/16 14:40, 8/28/16 18:20, 10

5, 28/08/2016, 8/28/16 18:25, 8/28/16 20:00, 5

6, 28/08/2016, 8/28/16 20:30, 8/28/16 21:55, 30

7, 28/08/2016, 8/28/16 22:15, 8/28/16 23:59, 20

];

IntervalMatch (Date) LOAD TimeIn, TimeOut Resident Timing;

LEFT JOIN(Timing)

LOAD * RESIDENT Times;

DROP TABLE Times;

NoConcatenate

FinalTiming:

LOAD

Shift,

Date,

Start,

End,

BreakInMins,

EmpID,

EmpName,

TimeIn,

TimeOut

RESIDENT Timing

WHERE Start >= TimeIn and End <= TimeOut;

DROP Table Timing;

To calculate Interval (front-end):

Interval(Sum(Aggr(TimeOut-TimeIn-Interval(Interval#(Sum(BreakInMins),'mm')),EmpID,TimeIn,TimeOut)),'mm')

Regards!!