topic Re: Value Label in Discussions

Value Label

Algerine — Sat, 10 Jun 2023 23:56:01 GMT

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!

Re: Value Label

ErraticAttack — Fri, 28 Oct 2022 00:35:03 GMT

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)

Re: Value Label

Algerine — Fri, 28 Oct 2022 17:46:27 GMT

Excellent! Thank you so much.