Row Problem

[#1]

I have a two part question. I am trying to change the value of previous rows in a column based on data in another column and I'm having a couple of problems. In the example below, when the status changes from Status1 to anything else (note green cells), I want to change the value of the cell 6 rows up (red cell).

The column formula I am using is below.

 

As you can see from the table, the script does insert a 99 in Row()-6 but there is also a 99 in Row(). I can't figure out where that last one is coming from.

The larger problem I'm trying to solve here is that I want to fill in all cells at and above the status change with 99 (So Row(), Row()-1, Row()-2, ... , Row()-6 all are 99). I tried to use a FOR loop to do this (see second script below) but it does nothing.

 

As I'm sure you can tell, I'm pretty ignorant regarding JSL so any assistance on this would be greatly appreciated. Thanks much.

[#2 · Accepted solution]

As I don't have your data table this is just a guess

Names Default To Here(1);

dt = New Table("Untitled 2",
	Add Rows(23),
	Compress File When Saved(1),
	New Column("S",
		Character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
			"A", "B", "B", "B", "C", "C"}
		)
	)
);

dt << New Column("R", Numeric, Continuous, Formula(
	If(:S == "A" & Lag(:S, -6) != "A",
		99
	, :S == "A",
		Lag(:R)
	);
));

You can use Lag to look for values behind and also forward.

[#3]

Thanks, I also want to know it.

[#4]

Thanks much for the help! I didn't know you could have negative lags. That's very handy.