Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted

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
Highlighted
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
Highlighted
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
Highlighted

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!

Highlighted
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

Highlighted

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

Thank you so much!!

Article Labels

    There are no labels assigned to this post.