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

Referencing a Column by Column Number in Formula in a For loop

Hi all,

 

I am trying to generate a bunch of new columns at once using a "for" loop, and each column will get named as the next item in the "sample" matrix. I can figure that part out, I think. But I also want each column to include a formula to look up a different column, which will be different each time, and subtract that value from the initial value (column "0"). When I run this it just gives me formula evaluation errors, not calculating what the column should be that it references.

Thanks for your help!

 

sample = {0, 1, 2, ...}
new columns = N Items (sample);

for( i=1, i<= new columns, i++, col = num(sample [i]);
	
	dt << New Column (sample [i] || " Days",  formula (as column (13+i+new columns) - :Name("0")))
	 	
	 );
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Referencing a Column by Column Number in Formula in a For loop

My error.  The string needs to be parsed to get rid of the pair of quotes

Names Default To Here( 1 );
dt = Current Data Table();
sample = {0, 1, 2};
new columns = N Items( sample );

For( i = 1, i <= new columns, i++,
	col = Num( sample[i] );
	colNamesList = dt << Get Column Names;
	Wait( 0 );
	Eval(
		Substitute(
				Expr(
					dt << New Column( sample[i] || " Days",
						formula( __column__ - :Name( "0" ) )
					)
				),
			Expr( __column__ ),
				Parse(":Name(\!"" || colNamesList[13 + i + new columns] || "\!")")
		)
	);
);
Jim

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: Referencing a Column by Column Number in Formula in a For loop

The issue with your current formula, is the variables "i".and "New Columns".  What you are assuming is that the calculation will be evalueated to a final calculation, and then placed into the As Column() function.  The issue is that what is placed into the formula is As Column( 13 + i + new columns ).  So when the formula is executed, it does not have the values of "i" and "new columns" when you created the column, but it uses whatever the values are whenever it calculates or recalculates the formulas.  And since "i" and "New Columns" is changing within your script, you have an issue.  Now you could change the formula to do the calculation before defining the formula, and then substitute in the column number into the formula.  However, the issue with this is that by using column numbers, if a column in the table is moved, or deleted, the column number in the formula will not be changed and therefore, the calculation will be wrong.  I suggest that you instead, substitute into the formula, the actual column name.  The below script illustrates the approach to do that.

Names Default To Here( 1 );
dt = Current Data Table();
sample = {0, 1, 2};
new columns = N Items( sample );

For( i = 1, i <= new columns, i++,
	col = Num( sample[i] );
	colNamesList = dt << Get Column Names;
	Wait( 0 );
	Eval(
		Substitute(
				Expr(
					dt << New Column( sample[i] || " Days",
						formula( __column__ - :Name( "0" ) )
					)
				),
			Expr( __column__ ),
				":Name(\!"" || colNamesList[13 + i + new columns] || "\!")"
		)
	);
);
Jim

Re: Referencing a Column by Column Number in Formula in a For loop

Thank you for your help!! This solution almost works - but I am stuck on one last bit.

 

Currently, the resulting spreadsheet gives me the following formula.

":Name(\!"5\!")" - 0

I want the first half to reference a column called "5", like the second half references a column called "0". No matter what I do, I can't get the first half to just show :Name("5"). When I reformat the quotation marks or try moving them, I just get variations on that theme.

 

Thanks again!

txnelson
Super User

Re: Referencing a Column by Column Number in Formula in a For loop

My error.  The string needs to be parsed to get rid of the pair of quotes

Names Default To Here( 1 );
dt = Current Data Table();
sample = {0, 1, 2};
new columns = N Items( sample );

For( i = 1, i <= new columns, i++,
	col = Num( sample[i] );
	colNamesList = dt << Get Column Names;
	Wait( 0 );
	Eval(
		Substitute(
				Expr(
					dt << New Column( sample[i] || " Days",
						formula( __column__ - :Name( "0" ) )
					)
				),
			Expr( __column__ ),
				Parse(":Name(\!"" || colNamesList[13 + i + new columns] || "\!")")
		)
	);
);
Jim

Re: Referencing a Column by Column Number in Formula in a For loop

Thank you so much!!