Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
DipeshVadgama
Partner - Creator II
Partner - Creator II

Calculating Hours Between Date & Time

Hi All,

I am trying to calculate total hour between each In & Out done by agents, But I am unable to do it.

Below is the example.

   

NameTimeDateDate/TimeIN/OutTotal Hour
A12:15 PM10-Apr-174/10/17 12:15 PMIn1:17
A1:32 PM10-Apr-174/10/17 1:32 PMOut
A2:48 PM10-Apr-174/10/17 2:48 PMIn2:47
A5:35 PM10-Apr-174/10/17 5:35 PMOut
A5:38 PM10-Apr-174/10/17 5:38 PMIn0:05
A5:44 PM10-Apr-174/10/17 5:44 PMOut
A5:45 PM10-Apr-174/10/17 5:45 PMInError In or Out is missing
B5:21 PM14-Apr-174/14/17 5:21 PMIn0:53
B6:14 PM14-Apr-174/14/17 6:14 PMOut
B6:18 PM14-Apr-174/14/17 6:18 PMIn1:04
B7:22 PM14-Apr-174/14/17 7:22 PMOut
B7:42 PM14-Apr-174/14/17 7:42 PMIn1:11
B8:54 PM14-Apr-174/14/17 8:54 PMOut
B12:12 PM10-Apr-174/10/17 12:12 PMIn0:34
B12:47 PM10-Apr-174/10/17 12:47 PMOutError In or Out is missing
B4:33 PM10-Apr-174/10/17 4:33 PMOutError In or Out is missing
C12:15 PM10-Apr-174/10/17 12:15 PMIn1:17
C1:32 PM10-Apr-174/10/17 1:32 PMOut
C2:48 PM10-Apr-174/10/17 2:48 PMIn2:47
C5:32 PM10-Apr-174/10/17 5:35 PMOut
C5:38 PM10-Apr-174/10/17 5:38 PMIn0:05
C5:44 PM10-Apr-174/10/17 5:44 PMOutError In or Out is missing
C9:40 PM10-Apr-174/10/17 9:40 PMOutError In or Out is missing
D5:21 PM14-Apr-174/14/17 5:21 PMIn0:53
D6:14 PM14-Apr-174/14/17 6:14 PMOut
D6:18 PM14-Apr-174/14/17 6:18 PMIn22:26
D4:44 PM10-Apr-174/10/17 4:44 PMOut
D4:48 PM10-Apr-174/10/17 4:48 PMIn0:17
D5:05 PM10-Apr-174/10/17 5:05 PMOut

14 Replies
DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

Thanks, This works perfect as needed.

DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

Hi,

Thanks for the help, I am again stuck. need a help again.

I am try to add Actual Login Date. If any agent login between 12.00 AM to 5.00 AM then date should be  minus 1 day else same date. I have attached demo file hope that will help.

effinty2112
Master
Master

Hi Dipesh,

I've added a couple of lines to the script shown in red.

Cheers

Andrew

RawData:

LOAD Name,

     Time,

     Date,

    Date(Floor(Timestamp([Date/Time], 'M/D/YY hh:mm TT')-5/24)) as [Actual Login Date],

     Timestamp([Date/Time], 'M/D/YY hh:mm TT') as [Date/Time],

     [IN/Out]

FROM

Book1.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1) Where Not IsNull(Name);

NoConcatenate

SortedData:

Load * Resident RawData Order by Name, [Date/Time];

NoConcatenate

CheckedData:

LOAD

*,

If(Status = 'OK' and [IN/Out] = 'Out', Interval([Date/Time]-[Prev Date/Time],'hh:mm')) as [Total Hours];

LOAD

Peek('ExcelRow',RecNo()-1,'SortedData') as ExcelRow,

Peek('Name',RecNo()-1,'SortedData') as Name,

Peek('Date',RecNo()-1,'SortedData') as Date,

Peek('Actual Login Date',RecNo()-2,'SortedData') as  [Actual Login Date],

Peek('Date/Time',RecNo()-1,'SortedData') as  [Date/Time],

Peek('IN/Out',RecNo()-1,'SortedData') as  [IN/Out],

Peek('Date/Time',RecNo()-2,'SortedData') as  [Prev Date/Time],

if(Peek('IN/Out',RecNo()-1,'SortedData') = 'In',

if(Peek('IN/Out',RecNo(),'SortedData') ='Out' and Peek('Name',RecNo(),'SortedData') = Peek('Name',RecNo()-1,'SortedData'), 'OK', 'Time Out missing'),

if(Peek('IN/Out',RecNo()-2,'SortedData') ='In' and Peek('Name',RecNo()-1,'SortedData') = Peek('Name',RecNo()-2,'SortedData'), 'OK', 'Time In missing')) as Status

AutoGenerate NoOfRows('SortedData');

Drop Tables RawData, SortedData;

DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

Thanks 🙂

DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

Thanks Andrew,

Appreciate if could able to help me similar problem.

Calculating Hours Between DateTime Stamp & Change Date