JSL - missing last value of the column when filled using a list

[#1]

Hello

When I am running a formula to assign one element from a list in a column for rows where an element of another list is displayed in another column, I encounter an unexpected issue : I am missing the value of the last row.

ID_List = {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48"};

Vessel_List = {"CS1-1", "CS1-2", "CS1-3", "CS1-4", "CS1-5", "CS1-6", "CS1-7", "CS1-8", "CS1-9", "CS1-10", "CS1-11", "CS1-12", "CS2-1", "CS2-2", "CS2-3", "CS2-4", "CS2-5", "CS2-6", "CS2-7", "CS2-8", "CS2-9", "CS2-10", "CS2-11", "CS2-12", "CS3-1", "CS3-2", "CS3-3", "CS3-4", "CS3-5", "CS3-6", "CS3-7", "CS3-8", "CS3-9", "CS3-10", "CS3-11", "CS3-12", "CS4-1", "CS4-2", "CS4-3", "CS4-4", "CS4-5", "CS4-6", "CS4-7", "CS4-8", "CS4-9", "CS4-10", "CS4-11", "CS4-12"};

dt << New Column( "Vessel NB",
  "Numeric",
  "Continuous",
   Formula(
	For( i = 1, i < N+1, i++,
		r= dt<<getRowsWhere( :Vessel ID ViCell == Vessel_List[i]);
		Column(dt, "Vessel NB")[r] = ID_List[i];	
	 ));
);

Here is how it looks like

I encountered the issue in multiple fields where I do the same exercise.

I found a workaround by taking the previous value (that I know is the same as I sorted the table).

// change data type of Vessel Nb to numeric continuous
Column("Vessel NB") << delete formula;
Column("Vessel NB") << unlock;	
Column("Vessel NB")<< data type(numeric)<<format("Best", 12) <<modeling type(continuous);

// bandaid solution : get previous Vessel number value to fill last line missing value
Nr=N Rows (dt);
Column(dt, "Vessel NB")[Nr] =Column(dt, "Vessel NB")[Nr-1];

However, while this workaround worked for my first table, when working on a second table I get the following message and don't manage to solve the issue.

I don't understand why I get this message whereas I clearly just created a column that is containing a formula and appears as locked.

Would you have a suggestion on how to solve this and why this occured in the first place?

Thank you in advance

Regards

NB : I am using JMP 16

[#2 · Accepted solution]

Hi,

I really would avoid a construction like this. Looking at the formula in that table you can see that there is a problem. You run into scoping problems (using a list defined outside in a formula), and you try to write the column itself within the formula in the column. Didn't know that JMP would let us ...

However I can reproduce your problem, but not exactly explain why.

So my proposal would be to loop outside like shown in the script below, it's much more transparent.

The loc() function gives you the position (index) in the list.

Another way would be to generate a table from your ID_LIST and Vessel_LIST,

and join that table to the table with reference dt.

Names Default To Here( 1 );

ID_List = {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24",
"25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48"};

Vessel_List = {"CS1-1", "CS1-2", "CS1-3", "CS1-4", "CS1-5", "CS1-6", "CS1-7", "CS1-8", "CS1-9", "CS1-10", "CS1-11", "CS1-12", "CS2-1", "CS2-2",
"CS2-3", "CS2-4", "CS2-5", "CS2-6", "CS2-7", "CS2-8", "CS2-9", "CS2-10", "CS2-11", "CS2-12", "CS3-1", "CS3-2", "CS3-3", "CS3-4", "CS3-5", "CS3-6",
"CS3-7", "CS3-8", "CS3-9", "CS3-10", "CS3-11", "CS3-12", "CS4-1", "CS4-2", "CS4-3", "CS4-4", "CS4-5", "CS4-6", "CS4-7", "CS4-8", "CS4-9", "CS4-10",
"CS4-11", "CS4-12"};

Show( N Items( ID_LIST ) );
Show( N Items( Vessel_LIST ) );

dt = New Table( "test", add rows( 48 ), New Column( "Vessel ID ViCell", Character, Nominal, set values( Vessel_list ) ) );
N = N Rows( dt );

dt << New Column( "row", "Numeric", "Continuous", Formula( For( i = 1, i <= N, i++, Column( dt, "row" )[i] = i ) ) );
dt << New Column( "Vessel NB",
	"Numeric",
	"Continuous",
	Formula(
		For( i = 1, i < N + 1, i++,
			r = dt << getRowsWhere( :Vessel ID ViCell == Vessel_List[i] );
			Column( dt, "Vessel NB" )[r] = ID_List[i];
		)
	)
);

// My proposal: run the loop outside, as the lists are outside, too
dt << New Column( "Vessel NB 2", "Numeric", "Continuous" );
For Each Row( dt, :Vessel NB 2 = ID_LIST[Loc( Vessel_LIST, :Vessel ID ViCell )[1]] );

[#3]

I am guessing a bit, but I believe you are doing a lot of work that is not required to get to your final answer.  Additionally, you are using values within your formats that are not native to the format.  That is, if you save your data table after running the format, quit your JMP session, start up a new JMP session and open your saved data table, the formula will error out.  The variables of "n", "Vessel_List" and "ID_Vessel" will no longer be defined in your new JMP session.  Therefore, the format will no longer run.

Could the below formula do what you want?

dt << New Column( "Vessel NB",
	"Numeric",
	"Continuous",
	Formula(
		currVessel = :Vessel ID ViCell;
		r = Current Data Table() << Get Rows Where( :Vessel ID ViCell == curVessel );
		r[n items(r)];
	)
);

[#4]

Hello Jim,

what I am trying to do is to "translate" the vessel ID into a vessel number. There are more than 48 rows, so I don't think your solution would help.

Thank you anyway

Regards

Céline

[#5]

Thank you Georg! This is working like a charm!

[#6]

I had a typo in my previous post.  Try the below JSL and see if you get the same results as @Georg 

Names Default To Here( 1 );
dt=current data table();
dt << New Column( "Vessel NB 3",
	"Numeric",
	"Continuous",
	Formula(
		currVessel = :Vessel ID ViCell;
		r = Current Data Table() << Get Rows Where( :Vessel ID ViCell == currVessel );
		try(r[n items(r)]);
	)
);