BookmarkSubscribe
Choose Language Hide Translation Bar
WHTseng
Community Trekker

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

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

 

0 Kudos
3 REPLIES 3

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

Try this simpler column formula:

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

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

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?

 

0 Kudos
Jeff_Perkinson
Community Manager Community Manager

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

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