21 Replies Latest reply: Feb 3, 2017 8:21 AM by Aurélien Martinez

# 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

• ###### Re: While loop

Hi,

the script:

Data:

DateTime,

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

[Min Value],

[Actual Value],

[Max Value],

If([Actual Value]<[Min Value] or [Actual Value]>[Max Value], 1, Null()) as test

FROM

(html, codepage is 1252, embedded labels, table is @1);

data2:

*,

if(IsNull(Peek(test2)) and Peek(Date) = Date, 1) as no

;

*,

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

Resident Data

Order by DateTime desc

;

DROP Tables Data;

sum(no)

Result;

Date sum(no)
3
01-01-20172
02-01-20171
• ###### Re: While loop

Hi Aurelien,

i am using the RangeSum(Above(Sum([Revenue]),0,RowNo())) for the CummulativeSum for the Revenue.

It is splitted by week1,week2,week3 and so on.

Three columns Weeks, Sum(Revenue) & CummulativeSum

When i select Week 1 or Week 2 the Sum(Revenue) and CummulativeSum values becomes equal

Is there any solution for this so that when i select Week2 it should show me the cummulative value for Week2 ?

• ###### Re: While loop

try this,

RangeSum(Above( Total Sum([Revenue]),0,RowNo()))

• ###### Re: While loop

Hi Aurelien,

Can you please explain your code from data2 table? I didnt understand the two load functions that you have used ?

Regards

• ###### Re: While loop

Hi,

I have used Preceding Load, but you can write :

data2:

*,

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

Resident Data

Order by DateTime desc

;

data3:

*,

if(IsNull(Peek(test2)) and Peek(Date) = Date, 1) as no

Resident data2

;

DROP Tables Data, data2;

• ###### Re: While loop

Oh Okay.. !! Thanks Aurelien. But what I fail to understand is when I use the same logic with the data in my excel file, the count doesn't seem to work. I have attached a sample of the excel and the qvf.

• ###### Re: While loop

It's just a date issue

ActualValues:

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:

*,

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

;

*,

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

Resident ActualValues

Order by DateTime desc

;

DROP Tables ActualValues;

• ###### Re: While loop

But that doesnt give me the right count of violation. The violation should only be counted when the temperature stabilizes or if it is a continuous violation. So the correct count for the dates 01/12, 02/12, 03/12, 04/12 should have been 2, 2, 2, 0 respectively and not what is displayed.

• ###### Re: While loop

ActualValues:

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:

*,

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

;

*,

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 ?

• ###### Re: While loop

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 :(

• ###### Re: While loop

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

• ###### Re: While loop

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

• ###### Re: While loop

Hi,

ActualValues:

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:

*,

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

;

*,

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

Resident ActualValues

Order by DateTime, Parameter desc

;

• ###### Re: While loop

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 ?

• ###### Re: While loop

ActualValues:
[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
[W:\HOME\QLIKVIEW\Bordel\Violations.xlsx]
(
ooxml, embedded labels, table is Sheet1);

data2:
*,
if(IsNull(Peek(test2)) and Peek(Date) = Date and test and Parameter=Peek(Parameter) and Peek([Device ID])=[Device ID], 1) as "VCount"

;

*,
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

• ###### Re: While loop

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

• ###### Re: While loop

Hi,

ActualValues:
[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:
*,
if(IsNull(Peek(test2)) and Peek(Date) = Date and test and Parameter=Peek(Parameter) and Peek([Device ID])=[Device ID], 1) as "VCount"
;
*,
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:
*,
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;

• ###### Re: While loop

Try

RangeSum(Above(Sum({<Week>}[Revenue]),0,RowNo()))

• ###### Re: While loop

Hi Anshu,

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

• ###### Re: While loop

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

• ###### Re: While loop

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