Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

While loop

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)

ParameterDateTimeMin ValueActual ValueMax Value

Temperature

01-01-2017 12:15:00 AM16

15

28
Temperature01-01-2017 02:30:00 AM161528
Temperature01-01-2017 05:42:00 AM1614.828
Temperature01-01-2017 07:20:00 AM1615.228
Temperature01-01-2017 09:00:02 AM 162328
Temperature01-01-2017 11:59:00 AM161428
Temperature02-01-2017 01:12:00 AM 162928
Temperature02-01-2017 05:12:00 PM1628.828

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

21 Replies
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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 ?

Help users find answers! Don't forget to mark a solution that worked for you!
Not applicable
Author

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 😞

Not applicable
Author

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

Not applicable
Author

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

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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

Help users find answers! Don't forget to mark a solution that worked for you!
Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

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

;

Help users find answers! Don't forget to mark a solution that worked for you!
Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

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

Help users find answers! Don't forget to mark a solution that worked for you!
Not applicable
Author

  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