Using For Loop to Create New Column

[#1]

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

[#2 · Accepted solution]

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]
		)
	)
);

[#3]

thank you