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

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

Jackieandrews — Wed, 19 Sep 2018 22:34:04 GMT

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

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

txnelson — Thu, 20 Sep 2018 06:00:45 GMT

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] || "\!")"
		)
	);
);

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

Jackieandrews — Thu, 20 Sep 2018 17:17:22 GMT

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!

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

txnelson — Thu, 20 Sep 2018 19:11:56 GMT

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] || "\!")")
		)
	);
);

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

Jackieandrews — Thu, 20 Sep 2018 20:24:02 GMT

Thank you so much!!