Value Label

[#1]

I'm trying to change values in multiple columns using a For loop and an If statement.  unfortunately, while it creates the new columns properly, the values in the second column are for some reason copied into the first column as well.  What am I missing?

New Table( "Untitled 11",
	Add Rows( 4 ),
	New Column( "Column_1",
		Character,
		"Nominal",
		Set Values( {"A1000", "A2000", "A3000", "A4000"} )
	),
	New Column( "Column_2",
		Character,
		"Nominal",
		Set Values( {"B5000", "B6000", "B7000", "B8000"} )
	)
);

OldColNames = { "Column_1", "Column_2" };

NewColNames = { "New_Column_1", "New_Column_2" };

nCols = N Items( OldColNames );

For( i = 1, i <= nCols, i++, 

	oldcol = OldColNames[i];
	newcol = NewColNames[i];

	New Column( newcol,
		Character,
		"Nominal",
		Formula(
			If(
				"A10" <= Left( As Column( oldcol ), 3 ) <= "A19", "A-One",
				"A20" <= Left( As Column( oldcol ), 3 ) <= "A29", "A-Two",
				"A30" <= Left( As Column( oldcol ), 3 ) <= "A39", "A-Three",
				"A40" <= Left( As Column( oldcol ), 3 ) <= "A49", "A-Four",
				"B50" <= Left( As Column( oldcol ), 3 ) <= "B59", "B-Five",
				"B60" <= Left( As Column( oldcol ), 3 ) <= "B69", "B-Six",
				"B70" <= Left( As Column( oldcol ), 3 ) <= "B79", "B-Seven",
				"B80" <= Left( As Column( oldcol ), 3 ) <= "B89", "B-Eight",
			)
		)
	);
);

Thanks!

[#2 · Accepted solution]

It is because the name oldcol inside of the formulas is only evaluated (resolved) when the formula is run, and the formula does not run when the column is created but at a later point.  At that later point both columns refer to oldcol, which now resolves to the same column

To fix this, you need to set the old col value at the time you're creating the column, like this:

New Table( "Untitled 11",
	Add Rows( 4 ),
	New Column( "Column_1",
		Character,
		"Nominal",
		Set Values( {"A1000", "A2000", "A3000", "A4000"} )
	),
	New Column( "Column_2",
		Character,
		"Nominal",
		Set Values( {"B5000", "B6000", "B7000", "B8000"} )
	)
);

OldColNames = { "Column_1", "Column_2" };

NewColNames = { "New_Column_1", "New_Column_2" };

nCols = N Items( OldColNames );

For( i = 1, i <= nCols, i++, 

	oldcol = OldColNames[i];
	newcol = NewColNames[i];

	Eval( Eval Expr(
	New Column( newcol,
		Character,
		"Nominal",
		Formula(
			As Constant( col = As Name( Expr( old col ) ) );
			If(
				"A10" <= Left( col, 3 ) <= "A19", "A-One",
				"A20" <= Left( col, 3 ) <= "A29", "A-Two",
				"A30" <= Left( col, 3 ) <= "A39", "A-Three",
				"A40" <= Left( col, 3 ) <= "A49", "A-Four",
				"B50" <= Left( col, 3 ) <= "B59", "B-Five",
				"B60" <= Left( col, 3 ) <= "B69", "B-Six",
				"B70" <= Left( col, 3 ) <= "B79", "B-Seven",
				"B80" <= Left( col, 3 ) <= "B89", "B-Eight",
			)
		)
	);
	) );
);

Here I'm using the Eval( Eval Expr( ... Expr( var ) ... ) ) construct to pre-evaluate part of the script, and the As Constant() evaluates only for the first row and just keeps that value for all following rows (it can speed up formulas by preventing the name lookup time)

[#3]

Excellent!  Thank you so much.