Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Problem in Getting Actual Duration in Mins

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,

UI

8 Replies
Highlighted

What are the issue?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
MVP & Luminary
MVP & Luminary

See this discussion: Task/Ticket Duration


talk is cheap, supply exceeds demand
Highlighted
Master
Master

Hi Umer,

                    Try:

Interval(End -Start-BreakInMins/1440)

Kind regards

Andrew

Highlighted
Specialist
Specialist

Hi,

check below

Breakdown time

Highlighted
Contributor III
Contributor III

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

UI

Highlighted
Master
Master

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?

Highlighted
Contributor III
Contributor III

Hi All Qlikers,

Did some work with the help of my colleagues.

image.png

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,

UI

Highlighted
Specialist III
Specialist III

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!!