Missing the data in final row while I add new column by loop

Question

Hi All,

I use formula to add a new column, but it seems the cell in the final row is blank.

Can you help to check what is wrong here?

Thank you.

dt = Open( "$SAMPLE_DATA/Cities.jmp" );

dt << New Column( "test_col_loop",
	character,
	formula(
		For( i = 1, i <= N Rows( dt ), i++,
			If( Column( "Region" )[i] == "MW",
				Column( "test_col_loop" )[i] = "IsMW",
				Column( "test_col_loop" )[i] = "NotMW"
			)
		)
	)
);

the result is like this:

Jeff_Perkinson · Answer

Except when there is no other choice, you should avoid using a For() loop in a formula column.

In a data table the formula is evaluated for each row. In your case that means that for every row of your table that whole loop is being executed.

I'd suggest that you spend some time using JMP interactively, adding formulas to columns using the Formula Editor to make sure you understand how it works before trying to add formulas with scripts.

The solution provided by @Melanie_J_Drake is the correct one.

The formula you've added to the data table is so unusual and ineffecient that it's not worth figuring out why it's behaving oddly in the last row. Even if we could figure it out it wouldn't be helpful.

WHTseng · Answer

Thank you.

But sometimes I still have to add column with complex formula, I take this example just to simplify my problem.

Can you help to tell why the final row cell is missing?

Melanie_J_Drake · Answer

Try this simpler column formula:

If( :Region == "MW",
	"IsMW",
	"NotMW"
)