cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
yanee
Level III

Using For Loop to Create New Column

Hi

 

I try to create the new column by using for loop

 

 

names default to here (1);
dt = current data table ();
summarize (PN=by (:Name("~DEVICE TYPE" )));

for (i=1, i <= N items(PN), i++,
new column("Cnt "||PN[i],
formula(If( :Name( "~DEVICE TYPE" ) == PN[i],
If( :SERIAL_NO || :TIME_DATE == Lag( :SERIAL_NO || :TIME_DATE, -1 ),0,1)
))));

 

 

I can create the new column but cannot get the formula work. The log file show below error message.

 

 

Column Cnt TRB100BC-08 Formula Interrupted
Subscript Range 1 times At rows: 2 Operation: PN[i], PN[/*###*/i]
Formula evaluation errors have been ignored

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Using For Loop to Create New Column

The main issue you have is that the Formula element within the New Column() function does not evaluate before processing, therefore, it will place into the formula your specification PN[i], and since the variable "i" is changing when your loop changes the formula for each of the columns you are creating will change.  So what you have to do, is to change the formula to be static values.  That is, you do not want the installed formula to have PN[i] but rather the actual evaluated value of whatever PN[i] is.  The Substitution() function allows for this to happen.  I think the below script will do what you want

Names Default To Here( 1 );
dt = Current Data Table();
Summarize( PN = by( :Name( "~DEVICE TYPE" ) ) );
PN={"X"}
For( i = 1, i <= N Items( PN ), i++,
	Eval(
		Substitute(
				Expr(
					New Column( __name__,
						formula(
							If( :Name( "~DEVICE TYPE" ) == __PN__,
								If(
									:SERIAL_NO || :TIME_DATE ==
									Lag( :SERIAL_NO || :TIME_DATE, -1 ),
									0,
									1
								)
							)
						)
					)
				),
			Expr( __name__ ), "Cnt " || PN[i],
			Expr( __PN__ ), PN[i]
		)
	)
);
Jim

View solution in original post

2 REPLIES 2
txnelson
Super User

Re: Using For Loop to Create New Column

The main issue you have is that the Formula element within the New Column() function does not evaluate before processing, therefore, it will place into the formula your specification PN[i], and since the variable "i" is changing when your loop changes the formula for each of the columns you are creating will change.  So what you have to do, is to change the formula to be static values.  That is, you do not want the installed formula to have PN[i] but rather the actual evaluated value of whatever PN[i] is.  The Substitution() function allows for this to happen.  I think the below script will do what you want

Names Default To Here( 1 );
dt = Current Data Table();
Summarize( PN = by( :Name( "~DEVICE TYPE" ) ) );
PN={"X"}
For( i = 1, i <= N Items( PN ), i++,
	Eval(
		Substitute(
				Expr(
					New Column( __name__,
						formula(
							If( :Name( "~DEVICE TYPE" ) == __PN__,
								If(
									:SERIAL_NO || :TIME_DATE ==
									Lag( :SERIAL_NO || :TIME_DATE, -1 ),
									0,
									1
								)
							)
						)
					)
				),
			Expr( __name__ ), "Cnt " || PN[i],
			Expr( __PN__ ), PN[i]
		)
	)
);
Jim
yanee
Level III

Re: Using For Loop to Create New Column

thank you