Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
First you need to change it as format from 24 Hours to 12 Hours and then check as Sum(FieldName)
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
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