Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have the below data with me wherein I am required to calculate the no. of violations in a day. The actual value should lie between the min and max value. If it doesn't, it should be counted as an violation. In the below case, the no. of violation for 01-01-2017 should be 2 because it is a continuous violation till it stabilizes once and then again violates (so count 2) and likewise for 02-01-2017 should be 1 (continuous violation)
Parameter | DateTime | Min Value | Actual Value | Max Value |
---|---|---|---|---|
Temperature | 01-01-2017 12:15:00 AM | 16 | 15 | 28 |
Temperature | 01-01-2017 02:30:00 AM | 16 | 15 | 28 |
Temperature | 01-01-2017 05:42:00 AM | 16 | 14.8 | 28 |
Temperature | 01-01-2017 07:20:00 AM | 16 | 15.2 | 28 |
Temperature | 01-01-2017 09:00:02 AM | 16 | 23 | 28 |
Temperature | 01-01-2017 11:59:00 AM | 16 | 14 | 28 |
Temperature | 02-01-2017 01:12:00 AM | 16 | 29 | 28 |
Temperature | 02-01-2017 05:12:00 PM | 16 | 28.8 | 28 |
I tried with a couple of do while loops but couldn't get the logic to include timestamp function to mark the end of the day and count afresh for a new day.
Regards
Thanks man! This is wonderful! Thanks a lot. What if I want to calculate duration of violation in hrs and minutes?
Hi,
ActualValues:
LOAD
[Device ID],
Parameter,
DateTime,
Date(Floor(Subfield(DateTime, ' ', 1))) as Date,
"Min Value",
"Actual Value",
"Max Value",
If("Actual Value"<"Min Value" or "Actual Value">"Max Value", 1, Null()) as test
Resident data2
;
data:
LOAD
*,
if(IsNull(Peek(test2)) and Peek(Date) = Date and test and Parameter=Peek(Parameter) and Peek([Device ID])=[Device ID], 1) as "VCount"
;
LOAD
*,
if(test and Peek(Date)*1 = Date*1 and Parameter=Peek(Parameter) and Peek([Device ID])=[Device ID], 1) as test2
Resident ActualValues
Order by [Device ID], Parameter, DateTime desc
;
data3:
LOAD
*,
Time(if(test and Peek(Date)*1 = Date*1 and Parameter=Peek(Parameter) and Peek([Device ID])=[Device ID], DateTime - Peek(DateTime) + Alt(Peek(time2), 0))) as time2
Resident data
Order By [Device ID], Parameter, DateTime asc
;
DROP Table ActualValues, data2, data;