topic Re: Need help with expressions, column references, formulas and column renaming in Discussions

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

miguello — Fri, 09 Jun 2023 16:59:44 GMT

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.

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

miguello — Fri, 20 May 2022 20:46:56 GMT

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.

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

txnelson — Fri, 20 May 2022 22:08:06 GMT

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

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

miguello — Fri, 20 May 2022 22:14:49 GMT

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.

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

miguello — Fri, 20 May 2022 22:17:41 GMT

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

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

ErraticAttack — Sat, 21 May 2022 01:08:45 GMT

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

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

jthi — Mon, 23 May 2022 13:57:16 GMT

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

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

miguello — Mon, 23 May 2022 23:22:34 GMT

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 :)

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

miguello — Mon, 23 May 2022 23:14:57 GMT

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