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

[Aurelien_Martinez]

ActualValues:

LOAD

    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

FROM [lib://amartinez35/Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

data2:

LOAD

  *,

  if(IsNull(Peek(test2)) and Peek(Date) = Date and test, 1) as "VCount"

;

LOAD

  *,

  if(test and Peek(Date)*1 = Date*1, 1) as test2

Resident ActualValues

Order by DateTime desc

;

DROP Tables ActualValues;

but for the 04/12 is a continuous violation ?

[#3]

Yes, for continuous violation, count 1. So 04/12 is 1. Somehow your code works magic for smaller data set but when I try to do the same for huge data entries, it flops 😞

[#4]

Hi Anshu,

LOAD Parameter,
DateTime ,
MinValue,
ActualValue,
MaxValue,
if(ActualValue<=MaxValue and ActualValue>=MinValue,0,1) as Voilation
FROM
[..\..\..\Users\\Desktop\Temperature.txt]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

In the chart you can add

Dimension -=Date#(Subfield(DateTime, ' ', 1), 'DD-MM-YYYY')

Measure - sum(Voilation)

Hope this helps you.

Thanks,

Prajna

[#5]

Thanks for the reply but this doesn't serve my purpose. My violation count has some conditions which I have mentioned above.

[Aurelien_Martinez]

In the field "Parameter" do you have other value than "Temperature Value" ?

[#7]

Hi Aurelien,

Yes, I do have another parameter value and also another column named Device ID. So I wish to calculate the violation for each device id for all days. Here's the actual file attached.

Regards

[Aurelien_Martinez]

Hi,

ActualValues:

LOAD

    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

FROM [lib://amartinez35/Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

data2:

LOAD

  *,

  if(IsNull(Peek(test2)) and Peek(Date) = Date and test and Parameter=Peek(Parameter),, 1) as "VCount"

;

LOAD

  *,

  if(test and Peek(Date)*1 = Date*1 and Parameter=Peek(Parameter), 1) as test2

Resident ActualValues

Order by DateTime, Parameter desc

;

[#9]

Thanks for replying Aurelien.. I did try this earlier but this code seems to work fine only for some 'Device ID' whereas for others, the violation count displayed is incorrect..What could be the possible reason ? Am I missing out something ?

[Aurelien_Martinez · Accepted solution]

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
FROM

(ooxml, embedded labels, table is Sheet1);

data2:
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
;

DROP Table ActualValues

[#11]

  The actual value should lie between the min and max value. If it doesn't, it should be counted as an violation.

As per above condition there is violation all 5 times in 1/1 right?

Thanks,

Prajna