topic Re: How to define a new column based on n consecutive rows condition in Discussions

How to define a new column based on n consecutive rows condition

HSS — Fri, 09 Jun 2023 17:00:25 GMT

Hi All,

I am looking for formula column where its elements depends upon the at least 10 consecutive rows of other column full filling a threshold value. Please see the attached image and data. Those also include the required column "Correct failer region".
Any help please.
Thanks, HSS

Re: How to define a new column based on n consecutive rows condition

jthi — Wed, 01 Jun 2022 11:18:02 GMT

is one option.

I changed Current column name:

Names Default To Here(1);

dt = Current Data Table();

dt << New Column("a", Numeric, Ordinal,
	<< Set Each Value(
		If(Row() == 1,
			val = 1; // this is breaking rule of > 750, so we define it separately
			change = 1;
		);

		next_vals = :Current[Row()::Min(Dim()[1], Row() + 9)];
		over_limit = Sum(next_vals >= 750);

		If(change & over_limit >= 10,
			val++;
			change = 0;
		, over_limit < 10,
			change = 1;
		);

		val;
	)
);

Re: How to define a new column based on n consecutive rows condition

HSS — Thu, 02 Jun 2022 02:25:25 GMT

Thanks Jarmo,

It is working perfectly. Now I have to integrate this in my script. I hope I can do that.
thanks, HSS

Re: How to define a new column based on n consecutive rows condition

HSS — Wed, 08 Jun 2022 08:28:20 GMT

Hi Jarmo and all,

This code is working fine and I am able to implement it in script perfectly fine.

But on the other hand, I am not able to understand it. I want to modify it a bit but not able to do that. Would you please help me in that -
1. What is this statement is doing -

next_vals = :Current[Row()::Min(Dim()[1], Row() + 9)];

2. I am not able to understand where the increment on 'change' is taking place and how this conditional can be satisfied ?

If(change & over_limit >= 10,

I believe it is same as - "If( change >=10 & over_limit >=10

3. In my current problem, I would like to increase the counter by one once the desired region is finished (with minimum 10 entries) -- something like this -

Any help ?

thanks, HSS

Re: How to define a new column based on n consecutive rows condition

HSS — Wed, 08 Jun 2022 08:31:12 GMT

There is a mistake in the attached image in previous message. Please ignore the last red rectangle, as by mistake, I have change the counter for old requirement too. That should not be the case.
thanks, HSS

Re: How to define a new column based on n consecutive rows condition

jthi — Wed, 08 Jun 2022 15:03:18 GMT

What I would suggest doing is modifying the script to something like this which will allow you to run it "row by row":

// Names Default To Here(1);
// Clear Log();
dt = Current Data Table();
run_script = Expr(
	If(Row() == 1,
		val = 1; // this is breaking rule of > 750, so we define it separately
		change = 1;
	);

	next_vals = :Current[Row()::Min(Dim()[1], Row() + 9)];
	over_limit = Sum(next_vals >= 750);

	If(change & over_limit >= 10,
		val++;
		change = 0;
	, over_limit < 10,
		change = 1;
	);
	val;
	show(row(), val, change, next_vals, over_limit);
	Write("\!N");
);

Row() = 1;
run_script;
Row() = 2;
run_script;
Row() = 5;
run_script;

Then you can fairly easily run the script in parts and look at the log to see what happens (you can also run one row at the time if you want to).

Script Window and Embedded Log:

Scripting Index is also helpful in explaining the functions, such as Dim().

If this won't be enough of a help, please comment in this post and tag me, I can try to explain it in more detail.

Re: How to define a new column based on n consecutive rows condition

HSS — Wed, 08 Jun 2022 15:57:43 GMT

@jthi Thanks for explanation.

I run it row by row and got a quite a bit.

Still I have no clue why do we have variable 'change' in the script and what is its purpose ? It is just taking two value, either 1 (as the set value) and 0 whenever we are getting more than 10 entries in a row with satisfying current value. But it does not appear anywhere in new column indexing. But if I am running it without variable 'Change' I do not get correct entries. But I am not able to figure out how and where does it impact our calculation/indexing.

and 3rd part of the problem - how can I increase the running index when the desired region of :current>=750 ends ?

thanks, HSS

Re: How to define a new column based on n consecutive rows condition

jthi — Wed, 08 Jun 2022 17:06:05 GMT

Change is used to increase val if needed. For problem three, it would require some re-writing on the algorithm as the conditions have changed

Re: How to define a new column based on n consecutive rows condition

HSS — Thu, 09 Jun 2022 03:07:03 GMT

Hello @jthi,

I tried something on my new requirement. It may not be the best solution but it is a work around. And it is very close to the solution you suggested for original problem. Till now it is working fine.

If( Row() == 1, val = 1 );
Next_10_values = Sum( :Current[Index( Row(), Min( Dim()[1], Row() + 9 ) )] >= 750 );
Previous_10_values = Sum( :Current[Index( Row(), Min( Dim()[1], Row() - 9 ) )] >= 750 );
If(
	Next_10_values == 10 & Lag( :Current ) < 750 | Previous_10_values == 9 & :Current < 750 &
	Lag( :Current ) >= 750,
	val = val + 1
);
val;

Many thanks for your support.
Regards, HSS

Re: How to define a new column based on n consecutive rows condition

jthi — Thu, 09 Jun 2022 05:59:12 GMT

I would maybe split the algorithm into three parts:

Increase counter when there is enough values over 750 (possibly set some support variable(s))
Increase counter when there is even one value below 750 AFTER and do this only ONCE for each step 1. increase (possibly set some support variable(s))
Don't increase counter (possibly set some support variable(s))

this might be easiest (maybe not easiest, but most clear) to do with some additional support variables.

Maybe something like this:

If(Row() == 1,
	counter = 1;
	allow_over_limit_change = 1;
	allow_below_limit_change = 0;
);

current_vals_to_check = :Current[Row()::Min(Dim()[1], Row() + 9)];
over_limit_count = Sum(current_vals_to_check >= 750);

If(allow_over_limit_change & over_limit_count >= 10,
	counter++;
	allow_below_limit_change = 1;
	allow_over_limit_change = 0;
, allow_below_limit_change & :Current < 750,
	counter++;
	allow_below_limit_change = 0;
	allow_over_limit_change = 1;
);
counter;

Row() == 1 if statement can most likely be replaced with this (in my opinion not as clear as using If statement)

As Constant(counter = 1);
As Constant(allow_over_limit_change = 1);
As Constant(allow_below_limit_change = 0);