Subscribe Bookmark RSS Feed

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

WHTseng

Occasional Contributor

Joined:

May 19, 2017

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:Picture1.jpg

 

3 REPLIES
melaniedrake

Staff

Joined:

May 30, 2014

Try this simpler column formula:

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

Occasional Contributor

Joined:

May 19, 2017

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?

 

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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 @melaniedrake 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.

-Jeff