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).

2024-02-19_11-50-48.png

The column formula I am using is below.

2024-02-19_12-13-53.png

 

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.

2024-02-19_12-19-16.png

 

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.