Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Name | Time | Date | Date/Time | IN/Out | Total Hour |
A | 12:15 PM | 10-Apr-17 | 4/10/17 12:15 PM | In | 1:17 |
A | 1:32 PM | 10-Apr-17 | 4/10/17 1:32 PM | Out | |
A | 2:48 PM | 10-Apr-17 | 4/10/17 2:48 PM | In | 2:47 |
A | 5:35 PM | 10-Apr-17 | 4/10/17 5:35 PM | Out | |
A | 5:38 PM | 10-Apr-17 | 4/10/17 5:38 PM | In | 0:05 |
A | 5:44 PM | 10-Apr-17 | 4/10/17 5:44 PM | Out | |
A | 5:45 PM | 10-Apr-17 | 4/10/17 5:45 PM | In | Error In or Out is missing |
B | 5:21 PM | 14-Apr-17 | 4/14/17 5:21 PM | In | 0:53 |
B | 6:14 PM | 14-Apr-17 | 4/14/17 6:14 PM | Out | |
B | 6:18 PM | 14-Apr-17 | 4/14/17 6:18 PM | In | 1:04 |
B | 7:22 PM | 14-Apr-17 | 4/14/17 7:22 PM | Out | |
B | 7:42 PM | 14-Apr-17 | 4/14/17 7:42 PM | In | 1:11 |
B | 8:54 PM | 14-Apr-17 | 4/14/17 8:54 PM | Out | |
B | 12:12 PM | 10-Apr-17 | 4/10/17 12:12 PM | In | 0:34 |
B | 12:47 PM | 10-Apr-17 | 4/10/17 12:47 PM | Out | Error In or Out is missing |
B | 4:33 PM | 10-Apr-17 | 4/10/17 4:33 PM | Out | Error In or Out is missing |
C | 12:15 PM | 10-Apr-17 | 4/10/17 12:15 PM | In | 1:17 |
C | 1:32 PM | 10-Apr-17 | 4/10/17 1:32 PM | Out | |
C | 2:48 PM | 10-Apr-17 | 4/10/17 2:48 PM | In | 2:47 |
C | 5:32 PM | 10-Apr-17 | 4/10/17 5:35 PM | Out | |
C | 5:38 PM | 10-Apr-17 | 4/10/17 5:38 PM | In | 0:05 |
C | 5:44 PM | 10-Apr-17 | 4/10/17 5:44 PM | Out | Error In or Out is missing |
C | 9:40 PM | 10-Apr-17 | 4/10/17 9:40 PM | Out | Error In or Out is missing |
D | 5:21 PM | 14-Apr-17 | 4/14/17 5:21 PM | In | 0:53 |
D | 6:14 PM | 14-Apr-17 | 4/14/17 6:14 PM | Out | |
D | 6:18 PM | 14-Apr-17 | 4/14/17 6:18 PM | In | 22:26 |
D | 4:44 PM | 10-Apr-17 | 4/10/17 4:44 PM | Out | |
D | 4:48 PM | 10-Apr-17 | 4/10/17 4:48 PM | In | 0:17 |
D | 5:05 PM | 10-Apr-17 | 4/10/17 5:05 PM | Out |
Thanks, This works perfect as needed.
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.
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;
Thanks 🙂
Thanks Andrew,
Appreciate if could able to help me similar problem.