While loop

[#1]

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

[#2]

Thanks man! This is wonderful! Thanks a lot. What if I want to calculate duration of violation in hrs and minutes?

[Aurelien_Martinez]

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;