cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Get the free JMP Student Edition for qualified students and instructors at degree granting institutions.
Choose Language Hide Translation Bar
View Original Published Thread

row operations

NetflixCrow956
Level III
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

2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: row operations

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]
	);
		
);

 

 

Jim

View solution in original post

ih
Super User (Alumni) ih
Super User (Alumni)

Re: row operations

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],

View solution in original post

5 REPLIES 5
jthi
Super User


Re: row operations

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_
	);
);
*/
-Jarmo
txnelson
Super User

Re: row operations

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]
	);
		
);

 

 

Jim
NetflixCrow956
Level III

Re: row operations

NetflixCrow956_0-1654084139990.png

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

NetflixCrow956
Level III

Re: row operations

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_
	);
)

NetflixCrow956_0-1654083507299.png

 

 

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

ih
Super User (Alumni) ih
Super User (Alumni)

Re: row operations

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],