topic Re: row operations in Discussions

row operations

NetflixCrow956 — Fri, 09 Jun 2023 17:00:18 GMT

For(x=1,x<=N Rows(dt),x++,
		If(IsMissing(Column(  dt, "Actual Finish Date_" )[x]),
		Column(  dt, "bouge pas" )[x] = Column(  dt, "Operation Due Date_" )[x] - :Operation Start Date_ [x-1],	
		Column(  dt, "bouge pas" )[x] = Column(  dt, "Actual Finish Date_" )[x] - :Actual Execution Start Date_ [x]
		);
		
	);

Hello, my problem is that I want in the first line of the for, the line x-1 for the column :Operation Start Date_ but this syntax does not work ? Thanks

Re: row operations

jthi — Tue, 31 May 2022 15:22:17 GMT

if you are looping over a data table's rows (usually I would try to avoid it and use formula or set each value) I would suggest using For Each Row() instead of For(). Without having example data I would guess that the issue could be that you will get 0 as row number, which isn't valid in JMP (Invalid Row Number at row 2 in access or evaluation of ':Operation Start Date_[ /*###*/x - 1]' , :Operation Start Date_[/*###*/x - 1])

Try setting x values manually and seeing what you get, add debug prints and take a look at log

Here is example script with couple of optional ideas on how you could set bouge pas columns values

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(3),
	New Column("Actual Finish Date_", Numeric, "Continuous", Format("Best", 12), Set Values([1, 4, 6])),
	New Column("Actual Execution Start Date_", Numeric, "Continuous", Format("Best", 12), Set Values([0, 2, 3])),
	New Column("Operation Start Date_", Numeric, "Continuous", Format("Best", 12), Set Values([10, 21, 32])),
	New Column("Operation Due Date_", Numeric, "Continuous", Format("Best", 12), Set Values([11, 22, 33])),
	New Column("bouge pas", Numeric, "Continuous")
);

For(x = 1, x <= N Rows(dt), x++,
	If(Is Missing(Column(dt, "Actual Finish Date_")[x]),
		Column(dt, "bouge pas")[x] = Column(dt, "Operation Due Date_")[x] - :Operation Start Date_[x - 1],
		Column(dt, "bouge pas")[x] = Column(dt, "Actual Finish Date_")[x] - :Actual Execution Start Date_[x]
	);
);

/* better option in my opinion
For Each Row(dt,
	If(Is Missing(:"Actual Finish Date_"n),
		:"bouge pas"n = :"Operation Due Date_"n - :Operation Start Date_[Row() - 1],
		"bouge pas"n = :"Actual Finish Date_"n - :Actual Execution Start Date_
	);
);
*/

/* usually best option 
Column(dt, "bouge pas") << Set Each Value(
	If(Is Missing(:"Actual Finish Date_"n),
		:"Operation Due Date_"n - :Operation Start Date_[Row() - 1],
		:"Actual Finish Date_"n - :Actual Execution Start Date_
	);
);
*/

Re: row operations

txnelson — Wed, 01 Jun 2022 12:08:58 GMT

If row 1's value for Actual Finish Date is missing, none of the currently proposed scripts will work. Therefore, that issue needs to be handled. The below code, a simple modification to the original JSL will work.

Note: I made a logic error on the original version of the code. I have changed it below.

For( x = 1, x <= N Rows( dt ), x++,
	If( Is Missing( Column( dt, "Actual Finish Date_" )[x] ),
		If( Row() > 1,
			Column( dt, "bouge pas" )[x] = Column( dt, "Operation Due Date_" )[x] - :Operation Start Date_[x - 1],
			Column( dt, "bouge pas" )[x] = 0; // Set the value if there is not an X-1 row (i.e. row 1)
		),
		Column( dt, "bouge pas" )[x] = Column( dt, "Actual Finish Date_" )[x] - :Actual Execution Start Date_[x]
	);
		
);

Re: row operations

NetflixCrow956 — Wed, 01 Jun 2022 11:40:38 GMT

For Each Row(dt,
	If(Is Missing(:"Actual Finish Date_"n),
		:"bouge pas"n = :"Operation Due Date_"n - :Operation Start Date_[Row() - 1],
		"bouge pas"n = :"Actual Finish Date_"n - :Actual Execution Start Date_
	);
)

It still doesn't work because from the first line I have empty lines in the column :Actual Finish Date_ and I don't know how to do it

Re: row operations

NetflixCrow956 — Wed, 01 Jun 2022 11:50:01 GMT

For example for lines 2, 3 4... the result is 6 days when it should be 1 day

Re: row operations

ih — Thu, 09 Jun 2022 11:16:34 GMT

If the first row has an empty Actual Finish Date_ then your script looks for the previous row, which will not work. You need to replace this line with something that doesn't reference the previous row:

		:"bouge pas"n = :"Operation Due Date_"n - :Operation Start Date_[Row() - 1],