BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
yanee
Occasional Contributor

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

 

 

0 Kudos
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
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
Occasional Contributor

Re: Using For Loop to Create New Column

thank you
0 Kudos