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]

Hi,

the script:

Data:

LOAD Parameter,

     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

[https://community.qlik.com/thread/247238]

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

data2:

LOAD

  *,

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

;

LOAD

  *,

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

Resident Data

Order by DateTime desc

;

DROP Tables Data;

In your chart:

sum(no)

Result;

Date	sum(no)
	3
01-01-2017	2
02-01-2017	1

[#3]

Hi Aurelien,

Can you please help me out here,

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 ?

[vinieme12]

Try

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

[#5]

Hi Aurelien,

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

Regards

[sravanthialuval]

try this,

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

[Aurelien_Martinez]

Hi,

I have used Preceding Load, but you can write :

data2:

LOAD

  *,

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

Resident Data

Order by DateTime desc

;

data3:

LOAD

  *,

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

Resident data2

;

DROP Tables Data, data2;

[#8]

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.

[Aurelien_Martinez]

It's just a date issue

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, 1) as "VCount"

;

LOAD

  *,

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

Resident ActualValues

Order by DateTime desc

;

DROP Tables ActualValues;

[#10]

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.