topic Re: Row Problem in Discussions

Row Problem

scott1588 — Mon, 19 Feb 2024 18:25:11 GMT

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.

Re: Row Problem

jthi — Mon, 19 Feb 2024 19:11:11 GMT

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.

Re: Row Problem

scott1588 — Tue, 20 Feb 2024 23:18:20 GMT

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

Re: Row Problem

VioletHartman — Tue, 12 Mar 2024 07:23:27 GMT

Thanks, I also want to know it.