Need help with expressions, column references, formulas and column renaming

[#1]

All, 

Need some help in the following case.

I have a name of a column in a string format.

I need to create a new column with formula based on the old one. I do it like so:

oldCol = "OldColumn";
Eval( Eval Expr( dt << New Column( "NewColumn", formula( As Column( Expr( oldCol ) ) ) ) ) );

Then later in the script I need to rename the old column. I do it like this:

Column(oldCol) << Set Name("NewOldColumn");

The problem now is that since I have the New Column's formula in the form of:

As Column("OldColumn")

it does not update when OldColumn changes name.

I would like to have it in the form of 

:OldColumn

but I don't know how to do that.

So, the question is: How do I need to modify expression:

Eval( Eval Expr( dt << New Column( "NewColumn", formula( As Column( Expr( oldCol ) ) ) ) ) );

so that it produces formula in the form of :OldColumn, and not in the form of As Column("OldColumn")?

Here is the full script to play with:

dt = New Table( "TestColumnsReferences",
	Add Rows( 3 ),
	New Column( "OldColumn", Character, "Nominal", Set Values( {"a", "b", "c"} ) )
);
oldCol = (dt << Get Column Names(string))[1];
//oldCol = "OldColumn";
Eval( Eval Expr( dt << New Column( "NewColumn", formula( As Column( Expr( oldCol ) ) ) ) ) );
Column(oldCol) << Set Name("NewOldColumn");

Thanks, M.

[#2]

I see one way of doing that (but rather not do it this way).

Get column names not as strings, but as references, and then just use Name Expr():

oldCol = (dt << Get Column Names())[1];Eval( Eval Expr( dt << New Column( "NewColumn", formula( Name Expr( oldCol ) ) ) ) );

but I already have name of the column as a string, and would like to stick with it.

[#3 · Accepted solution]

Here is one approach that works for me.  I have expanded the naming to handle complex JMP column names.  This confuses the reader a bit, but makes the method more powerful

dt = New Table( "TestColumnsReferences",
	Add Rows( 3 ),
	New Column( "OldColumn+one", Character, "Nominal", Set Values( {"a", "b", "c"} ) )
);
oldCol = (dt << Get Column Names( string ))[1];
//oldCol = "OldColumn";
//Eval( Eval Expr( dt << New Column( "NewColumn", formula( As Column( Expr( oldCol ) ) ) ) ) );
Eval(
	Substitute(
			Expr(
				dt << New Column( "NewColumn", formula( __oldCol__ ) )
			),
		Expr( __oldCol__ ), parse(":\!"" || oldCol || "\!"n")
	)
);
Column( oldCol ) << Set Name( "NewOldColumn" );

[#4]

Yep, essentially the same approach as I showed below, only use Substitute instead of Eval Insert, right?

[#5 · Accepted solution]

One more option is combination of Eval(), EvalExpr(), Expr(), Name Expr() and As Column()

Names Default To Here(1);

dt = New Table("TestColumnsReferences", Add Rows(3), 
	New Column("OldColumn", Character, "Nominal", Set Values({"a", "b", "c"}))		
);

oldCol = (dt << Get Column Names(string))[1];

//oldCol = "OldColumn";
Eval(Eval Expr(dt << New Column("NewColumn", formula(Expr(Name Expr(AsColumn(oldCol)))))));
Column(oldCol) << Set Name("NewOldColumn");

[#6]

Problem with "As Column" as I explained in the question is that is stays in the formula, and then when I later change name of the column that I refer to in the column (and I do change it) the formula becomes invalid

My apologies, with your combination Expr --> Name Expr --> As Column it does work! It turns As Column("string") into :string!

I've been given several solutions here, and I really liked the one with JSL Quote, but this is my new favorite. Will have to re-do the script all over again

[#7 · Accepted solution]

Ok, I figured it out. For the formula part just use the following chain:

 Name Expr(Expr(Parse(Eval Insert(":#oldCol#", startChar="#"))))

Had to use startChar="#" since in real script formula actually uses REgEx where "^" is used.

So the full script is this:

dt = New Table( "TestColumnsReferences",
	Add Rows( 3 ),
	New Column( "OldColumn", Character, "Nominal", Set Values( {"a", "b", "c"} ) )
);
oldCol = (dt << Get Column Names(string))[1];
//oldCol = "OldColumn";
Eval( Eval Expr( dt << New Column( "NewColumn", formula( Name Expr(Expr(Parse(Eval Insert(":#oldCol#", startChar="#")))) ) ) ) );
Column(oldCol) << Set Name("NewOldColumn");

This way formula has :OldColumn rather than As Column("OldColumn") and you can now safely rename columns without breaking formula.

[#8 · Accepted solution]

Personally I like using the JSL Quote() function to maintain syntax highlighting and lexical parsing -- then just pass the value into the :Name() directive, as such

dt = New Table( "TestColumnsReferences",
	Add Rows( 3 ),
	New Column( "OldColumn", Character, "Nominal", Set Values( {"a", "b", "c"} ) )
);
oldCol = (dt << Get Column Names(string))[1];
//oldCol = "OldColumn";
Eval( Parse( Eval Insert( JSL Quote(
	dt << New Column( "NewColumn", Formula( :Name("^oldCol^") ) )
) ) ) );
Column(oldCol) << Set Name("NewOldColumn");

[#9]

Thank you for the tip on JSL Quote()! It really is much cleaner now that I don't have to escape all the quotes.