sum time duration with over lapping rows

[adinepstein]

Hi,

See the following table:

Controller	Day	Test_Bed	Status	start Time	end Time	Duration
bwdilabdt1320	6/30/2017	#1	Busy	30/6/2017 12:00:00 PM	30/6/2017 17:59:59 PM	6.00
bwdilabdt1320	6/30/2017	#2	Busy	30/6/2017 13:00:00 PM	30/6/2017 19:59:59 PM	7.00
bwdilabdt1320	6/30/2017	#3	Busy	30/6/2017 03:00:00 AM	30/6/2017 04:59:59 AM	2.00
bwdilabdt1444	7/1/2017	#1	Locked	1/7/2017 8:50:55 PM	1/7/2017 8:51:45 PM	0.01
bwdilabdt1444	7/1/2017	#1	Busy	1/7/2017 9:13:06 PM	1/7/2017 11:59:59 PM	2.78
bwdilabdt1965	7/1/2017	#1	Free	1/7/2017 12:00:00 AM	1/7/2017 8:50:55 PM	20.85

I'm trying to add a column that will sum up the time duration for each controller-day-status reducing the over laps of the different test beds - if two test beds on the same controller have the same status at the same time it will count it once.

for example: the first 3 rows are under the same controller on the 6/30/2017 there are 3 different test beds with over lapping times that are in status busy, in the new column the total duration will be 10.00 hours and not 15.00 hours (in row 1 and 2 there is over lapping between 13:00-17:59:59).

Thanks you,

Adin

[Anil_Babu_Samineni]

First you need to change it as format from 24 Hours to 12 Hours and then check as Sum(FieldName)

[effinty2112]

Hi Adin,

               I've posted a script but you should know that I had to tidy your data up a bit.

Some of your times didn't make sense, for example we had times like 13:00 PM. If you're using AM/PM then the hour cannot exceed 12. QlikView will not accept a timestamp formatted like that. A smaller matter was that we had test bed 1 both free and locked at 1/7/2017 20:50:55.

Try this script

Data:

LOAD

Controller,

Day,

Test_Bed,

Status,

Timestamp([Start Time],'D/M/YYYY hh:mm') as [Start Time],

Timestamp([End Time],'D/M/YYYY hh:mm') as [End Time];

LOAD * INLINE [

    Controller, Day, Test_Bed, Status, Start Time, End Time, Duration

    bwdilabdt1320, 6/30/2017, #1, Busy, 30/6/2017 12:00:00, 30/6/2017 17:59:59, 6.00

    bwdilabdt1320, 6/30/2017, #2, Busy, 30/6/2017 13:00:00, 30/6/2017 19:59:59, 7.00

    bwdilabdt1320, 6/30/2017, #3, Busy, 30/6/2017 03:00:00, 30/6/2017 04:59:59, 2.00

    bwdilabdt1444, 7/1/2017, #1, Locked, 1/7/2017 20:50:55, 1/7/2017 20:51:45, 0.01

    bwdilabdt1444, 7/1/2017, #1, Busy, 1/7/2017 21:13:06, 1/7/2017 23:59:59, 2.78

    bwdilabdt1965, 7/1/2017, #1, Free, 1/7/2017 00:00:00, 1/7/2017 20:50:55, 20.85

];

TempMinMax:

LOAD

Min([Start Time]) as FirstMinute,

Max([End Time]) as LastMinute

Resident Data;

Let vFirstMinute = Round(Peek('FirstMinute',0,'TempMinMax'),1/24/60)*24*60;

Let vLastMinute = Round(Peek('LastMinute',0,'TempMinMax'),1/24/60)*24*60;

Let vNumMin = ($(vLastMinute) - $(vFirstMinute));

DROP Table TempMinMax;

Clock:

LOAD

Timestamp(Temp/24/60,'D/M/YYYY hh:mm') as Clocktime;

LOAD

$(vFirstMinute) + RecNo() -1 as Temp

AutoGenerate $(vNumMin);

IntervalMatchTable:

IntervalMatch(Clocktime) LOAD [Start Time], [End Time] Resident Data;

Now hopefully this straight table will give you what you need.

Two expressions:

Minutes: count(Clocktime)

Distinct Minutes: count(Distinct Clocktime)

Controller	Day	Status	Minutes	Distinct Minutes
			3000	2700
bwdilabdt1320	6/30/2017	Busy	900	600
bwdilabdt1444	7/1/2017	Busy	166	166
bwdilabdt1444	7/1/2017	Locked	1	1
bwdilabdt1965	7/1/2017	Free	1251	1251
			682	682

The Distinct Minutes column is, I think, the figure you need.

yAlso, you can create simple visualisations that show the test bed use over time that shows clearly the overlap times.

Regards

Andrew

[antoniotiman]

Hi Adin,

I have purge AM/PM from Time because timedtamp isn't correct

Temp:
LOAD Controller,
Day,
Test_Bed,
Status,
PurgeChar([start Time],'APM') as [start Time],
PurgeChar([end Time],'APM') as [end Time]
Inline [
Controller, Day, Test_Bed, Status, start Time, end Time,Duration
bwdilabdt1320, 6/30/2017, #1, Busy, 30/6/2017 12:00:00 PM, 30/6/2017 17:59:59 PM, 6.00
bwdilabdt1320, 6/30/2017, #2, Busy, 30/6/2017 13:00:00 PM, 30/6/2017 19:59:59 PM, 7.00
bwdilabdt1320, 6/30/2017, #3, Busy, 30/6/2017 03:00:00 AM, 30/6/2017 04:59:59 AM, 2.00
bwdilabdt1444, 7/1/2017, #1 ,Locked, 1/7/2017 8:50:55 PM, 1/7/2017 8:51:45 PM, 0.01
bwdilabdt1444, 7/1/2017, #1 ,Busy, 1/7/2017 9:13:06 PM, 1/7/2017 11:59:59 PM, 2.78
bwdilabdt1965, 7/1/2017, #1 ,Free, 1/7/2017 12:00:00 AM, 1/7/2017 20:50:55 PM, 20.85];

Temp_1:
NoConcatenate LOAD Controller,Day,Test_Bed,Status,
If(Controller=Peek(Controller) and Day=Peek(Day) and Status=Peek(Status),
If([start Time] > Peek([start Time]) and [start Time] < Peek([end Time]), Peek([start Time]),[start Time]),[start Time]) as [start Time],
If(Controller=Peek(Controller) and Day=Peek(Day) and Status=Peek(Status),
If([start Time] > Peek([start Time]) and [end Time] > Peek([end Time]),[end Time],Peek([end Time])),[end Time]) as [end Time],
If(Controller=Peek(Controller) and Day=Peek(Day) and Status=Peek(Status),
If([start Time] > Peek([start Time]) and [start Time] < Peek([end Time]),1,0),1) as Flag
Resident Temp Order By Controller,Day,Status,[start Time] ;
Drop Table Temp;
NoConcatenate LOAD *,Interval(Round([end Time]-[start Time],1/1440),'hh:mm') as Duration
Resident Temp_1 Where Flag = 1;
Drop Table Temp_1;

See Attachment

Regards,

Antonio