JSL failing in version 19 but works in 17

[#1]

I just upgraded to JMP 19 from JMP 17. I have a long and complicated routine that works well when I use it in JMP 17 but fails when I try it in 19. There may be more failures but the first one I am encountering is related to selecting rows to be modified to fix known errors in the data I imported. When debugging, I can see that the Get Rows Where command is returning an empty matrix. I confirmed that the targeted row is in the data table and I can force the program to select it by entering the criteria in the menu windows.

row_to_update = newContrib << Get Rows Where(:"request_id" == 263496 & :"line_no" == 0); 

The subsequent command fails to make a desired correction on that row because row_to_update returns this:     [](0, 1)
newContrib was defined earlier as a data table. When I hover over the variable name, the pop-up has the right table. 

Based on the documentation, I tried modifying the line to use Where instead but failed identically.

row_to_update = newContrib[Where(:"request_id" == 263496 & :"line_no" == 0)];

I could not find anything in release notes. I seem to have the right syntax according to the help manuals.
Could someone please explain what has changed that this no longer works and maybe how to fix it?

Much appreciated!

Nancy

[#2 · Accepted solution]

<< Get Rows Where does still work that way in JMP 19.  Your second command (using Where()) should change a bit.

Names Default To Here( 1 );
dt= Open( "$SAMPLE_DATA/Big Class.jmp" );
row_to_update = dt<< Get Rows Where( :"name" == "JANE" & :"age" == 12 );	//[3]
row_to_update2 = Where( dt, :name == "JANE" & :age == 12 );		//[3]

row_to_update = Where( newContrib, :request_id == 263496 & :line_no == 0 );

As for what the issue might be?  Check to make sure the data types are correct.  Does JMP 19 pull in those columns as character, while JMP 17 did as numeric?  It's likely something simple like that, since the syntax for << Get Rows Where has not changed.

[#3]

Thank you for the explanation on correct syntax for Where, for confirming the Get Rows Where syntax had not changed, and most importantly for suggesting that I check the data types! That was in fact the problem (integers coming in as characters). In fact, all my data on a dozen sheets is being imported as characters now when it was recognized as various types automatically before. It's such a fundamental import behavior change that it never occurred to me to check, so I really appreciate the reminder that I forgot to go back to basics! Besides, adding lines to change data types for columns or revisiting the settings on my open command is a much easier repair than changing the syntax on the hundreds of lines of code that use "Get Rows Where"!

 Many thanks,

Nancy

[#4]

I'm glad it's working for you.  One thing I would add is that it may be worth it to you to update your script to use Where().  The performance improvement is quite significant.  As an example, I ran this code on a table with one million rows.  Where() was about an order of magnitude faster than <<Get Rows Where.  Perhaps you could do a Find and Replace in your script?

Names Default to Here( 1 );
dt = Current Data Table();
now = HP Time();
row_to_update = dt << Get Rows Where( :request_id == 220681 & :line_no == 70 );
then = HP Time();
elapsed = then - now;
Show( elapsed );	//119053
Wait( 2 );
now = HP Time();
row_to_update = Where( dt, :request_id == 220681 & :line_no == 70 );
then = HP Time();
elapsed = then - now;
Show( elapsed );	//11777

[#5]

Hi Nancy,

The [](0,1) means row_to_update is an empty list, so there aren't any matching rows in the data table. Since Get Rows Where () returns a list, you might be better served with the different approach in the script below:

names default to here (1);

newContrib = New Table( "newContrib",
	Add Rows( 3 ),
	New Column( "request_id",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [263496, 263496, 263496] )
	),
	New Column( "line_no",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [0, 0, 1] )
	)
);

row_to_update = newContrib << Get Rows Where(:"request_id" == 263496 & :"line_no" == 0);
//row_to_update is [1,2]
row_to_update = newContrib << Get Rows Where(:"request_id" == 263497 & :"line_no" == 0);
//row_to_update is empty: [](0, 1)
row_to_update = (newContrib << Get Rows Where(:"request_id" == 263496 & :"line_no" == 0))[1];
//row_to_update is 1 

//a different approach:
rows_to_update = newContrib << Get Rows Where(:"request_id" == 263496 & :"line_no" == 0);
if (n items(rows_to_update) > 0, row_to_update = rows_to_update[1]);