topic Re: Calculating Hours Between Date & Time in QlikView

Calculating Hours Between Date & Time

DipeshVadgama — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculating Hours Between Date & Time

Anil_Babu_Samineni — Fri, 08 Sep 2017 11:30:25 GMT

First work on Date Field

Date(Date#([Date/Time],'MM/DD/YYYY hh:mm TT'),'DD-MM-YYYY HH:MM:SS TT') as [Date/Time]

Then, For [Total Hours] you can use this?

Interval(DateTime - Previous(DateTime),'hh:mm')

Re: Calculating Hours Between Date & Time

Peter_Cammaert — Fri, 08 Sep 2017 11:30:45 GMT

What is it that your are unable to accomplish? Can you post what you already have?

Also, it seems awkward that you use the In rows as terminal rows. What is the reason for attaching the period of time to the In row? And why do some Out rows carry an error message when there is a corresponding In row that reported a correct period? For example, see B row with time stamp 12:47PM.

Re: Calculating Hours Between Date & Time

Kushal_Chawda — Fri, 08 Sep 2017 11:36:31 GMT

Data:

LOAD

Name,

Date,

Time,

[Date/Time],

[In/Out]

FROM data;

Final:

noconcatenate

LOAD *,

if([In/Out]='In' and previous([In/Out])='Out', interval(previous([Date/Time])-[Date/Time],'hh:mm'),'Out is Missing') as TotalHour

Resident Data

order by Name,Date,Time,[In/Out] desc;

drop table Data;

Re: Calculating Hours Between Date & Time

DipeshVadgama — Fri, 08 Sep 2017 14:00:29 GMT

Hi,

I tried but I am getting wrong results.

Re: Calculating Hours Between Date & Time

Kushal_Chawda — Fri, 08 Sep 2017 14:03:47 GMT

Data:

LOAD

Name,

Date,

Time,

[Date/Time],

[In/Out]

FROM data;

Final:

noconcatenate

LOAD *,

if([In/Out]='In' and previous([In/Out])='Out', interval(previous([Date/Time])-[Date/Time],'hh:mm'),'Out is Missing') as TotalHour

Resident Data

order by Name,Date,Time asc ,[In/Out] desc;

drop table Data;

Re: Calculating Hours Between Date & Time

Kushal_Chawda — Fri, 08 Sep 2017 14:06:36 GMT

It will be better if you can share the sample data

Re: Calculating Hours Between Date & Time

Kushal_Chawda — Fri, 08 Sep 2017 14:33:51 GMT

or you can create the straight table

Dimension:

Name,Date,Time,Date/Time,In/Out

Expression:

=if([IN/Out]='In' and Below(total [IN/Out])='Out',Interval(Below(TOTAL [Date/Time])-[Date/Time],'hh:mm:ss'),

if([IN/Out]='Out' and Above(total [IN/Out])='In',Interval([Date/Time]-above(total [Date/Time]),'hh:mm:ss'),

if([IN/Out]='In' and Below(total [IN/Out])='In','Out is missing')))

Note: Sort all the dimension ascending with the same order as written

Re: Calculating Hours Between Date & Time

DipeshVadgama — Mon, 11 Sep 2017 06:14:27 GMT

Hi Please find example file.

Re: Calculating Hours Between Date & Time

effinty2112 — Mon, 11 Sep 2017 10:35:23 GMT

Hi Dipesh,

Try this script:

RawData:

LOAD Name,

Time,

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('Name',RecNo()-1,'SortedData') as Name,

Peek('Date',RecNo()-1,'SortedData') as 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;

To get:

Name	Date	Date/Time	IN/Out	Status	Total Hours
A	10/04/2017	4/10/17 12:15 PM	In	OK
A	10/04/2017	4/10/17 01:32 PM	Out	OK	01:17
A	10/04/2017	4/10/17 02:48 PM	In	OK
A	10/04/2017	4/10/17 05:35 PM	Out	OK	02:47
A	10/04/2017	4/10/17 05:38 PM	In	OK
A	10/04/2017	4/10/17 05:44 PM	Out	OK	00:05
A	10/04/2017	4/10/17 05:45 PM	In	Time Out missing
B	10/04/2017	4/10/17 12:12 PM	In	OK
B	10/04/2017	4/10/17 12:47 PM	Out	OK	00:34
B	10/04/2017	4/10/17 04:33 PM	Out	Time In missing
B	14/04/2017	4/14/17 05:21 PM	In	OK
B	14/04/2017	4/14/17 06:14 PM	Out	OK	00:53
B	14/04/2017	4/14/17 06:18 PM	In	OK
B	14/04/2017	4/14/17 07:22 PM	Out	OK	01:04
B	14/04/2017	4/14/17 07:42 PM	In	OK
B	14/04/2017	4/14/17 08:54 PM	Out	OK	01:11
C	10/04/2017	4/10/17 12:15 PM	In	OK
C	10/04/2017	4/10/17 01:32 PM	Out	OK	01:17
C	10/04/2017	4/10/17 02:48 PM	In	OK
C	10/04/2017	4/10/17 05:35 PM	Out	OK	02:47
C	10/04/2017	4/10/17 05:38 PM	In	OK
C	10/04/2017	4/10/17 05:44 PM	Out	OK	00:05
C	10/04/2017	4/10/17 09:40 PM	Out	Time In missing
D	10/04/2017	4/10/17 04:44 PM	Out	Time In missing
D	10/04/2017	4/10/17 04:48 PM	In	OK
D	10/04/2017	4/10/17 05:05 PM	Out	OK	00:17
D	14/04/2017	4/14/17 05:21 PM	In	OK
D	14/04/2017	4/14/17 06:14 PM	Out	OK	00:53
D	14/04/2017	4/14/17 06:18 PM	In	Time Out missing

Regards

Andrew

Re: Calculating Hours Between Date & Time

DipeshVadgama — Mon, 11 Sep 2017 12:27:57 GMT

Thanks, This works perfect as needed.

Re: Calculating Hours Between Date & Time

DipeshVadgama — Wed, 13 Sep 2017 11:18:20 GMT

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.

Re: Calculating Hours Between Date & Time

effinty2112 — Wed, 13 Sep 2017 13:51:51 GMT

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;

Re: Calculating Hours Between Date & Time

DipeshVadgama — Wed, 13 Sep 2017 14:55:51 GMT

Thanks 🙂

Re: Calculating Hours Between Date & Time

DipeshVadgama — Wed, 13 Sep 2017 14:59:16 GMT

Thanks Andrew,

Appreciate if could able to help me similar problem.

Calculating Hours Between DateTime Stamp & Change Date