Some Insights on Dynamic Column Formulas

scott1588 — Mon, 12 Aug 2024 18:38:18 GMT

Being a JSL beginner, I find I often struggle with script interpretation. If I find a script in a discussion that seems to address my problem and try to customize it, I often break it and have a hard time understanding how to fix it. During my JSL scripting journey I have heavily relied on this forum for assistance -- both searching the extensive archives for past answers and asking for help if the answers are not apparent. This forum is an invaluable resource for learning JSL and the depth of knowledge here and willingness to share is amazing.

Recently, I've been playing around with dynamic column formulas and came across a very helpful post from a few years ago here. The solution from @txnelson and the follow up suggestion from @NisKorsgaard were quite revealing to me. Because I am so new to JSL, I often like to "unpack" the code to figure out what each piece does. I did this for the Dynamic Column Formula problem I was having. The exercise was very helpful for me and I thought it might also be for others so I have included the "unpacked" code below.

The script does nothing practical but provides a simple example of how column formulas may be dynamically changed. Hopefully someone will find this useful.

// CALL COLUMN BY REFERRING TO VARIABLE
// See this Discussion for more info:
// From: https://community.jmp.com/t5/Discussions/call-a-column-by-referring-to-variable/td-p/272339


// Original script from @txnelson

//Names Default To Here( 1 );
//dt = Open( "$SAMPLE_DATA/big class.jmp" );
//columnname1 = Column( dt, 4 ) << Get Name;
//columnname2 = Column( dt, 5 ) << Get Name;
//
//Eval(
//	Substitute(
//			Expr(
//				dt << New Column( "new_" || columnname1,
//					Numeric,
//					continuous,
//					formula( __c1__ + __c2__ )
//				)
//			),
//		Expr( __c1__ ), Parse( ":" || columnname1 ),
//		Expr( __c2__ ), Parse( ":" || columnname2 )
//	)
//);




// ==================================================
// REVISED SCRIPT
// Pulls apart the above script a bit. For me, this was easier to visualize
// and understand. Also helped clarify how variables work and why the
// EVAL EXPR function is needed. When running script look at the log for changing
// variable contents.



// SETUP
// **************************************************

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/big class.jmp" );
columnName1 = Column( dt, 4 ) << Get Name;
columnName2 = Column( dt, 5 ) << Get Name;



// FORMULA WITH PLACEHOLDERS
// Create expression with desired formula using placeholders for each variable and store result in var.
// **************************************************

var = Expr(
	dt << New Column( "new_" || columnName1, Formula( __c1__ + __c2__ ) )
);

Show (var); // For learning and debugging. Look in log for value of var.



// SWAP OUT PLACEHOLDERS FOR COLUMN NAME STORED IN VARIABLES
// Replace placeholders with variable contents, make them column names and store result in subVar.
// To see the effect of EVAL EXPR, try substituting EVAL (remove EXPR) and re-running the script.
// Look at the log.
// **************************************************

subVar = Substitute(
	Eval Expr( var ),
	Expr( __c1__ ), As Name( columnName1 ),
	Expr( __c2__ ), As Name( columnName2 )
);

Show( subVar);  // For learning and debugging. Look in log for value of subVar.



// DO IT
// Make the column with the new formula.
// **************************************************
	
Eval( subVar );

Re: Some Insights on Dynamic Column Formulas

jthi — Mon, 12 Aug 2024 19:06:50 GMT

This is a bit old post but a good starting point . When I have enough time I will write my view about these (expressions and handling them in few different cases) as a blog post.

Re: Some Insights on Dynamic Column Formulas

hogi — Mon, 12 Aug 2024 19:58:31 GMT

@jthi , thanks for this introductory course about the basics of expression handling :

33min - ...

another great lecture:

Eval(Substitute()) is one of a few tools you should carry like a Swiss army knife for JSL scripting : )

Re: Some Insights on Dynamic Column Formulas

hogi — Tue, 13 Aug 2024 09:37:00 GMT

Instead of storing the column names as strings and converting them to "names" via as name, one can also store the names (with or w/o ":") - and retrieve and conserve them via Name Expr:

dt = Open( "$SAMPLE_DATA/big class.jmp" );
col1 = Name Expr(:height);
col2 = Name Expr(:weight);

new name = "new_" || (col1 << get name);

col1 << set name("tmp"); // we don't care - the reference will still point to the right column 

var = Expr(
	dt << New Column( new name , Formula( __c1__ + __c2__ ) )
);

var = Substitute(	Name Expr(var),
	Expr( __c1__ ), Name Expr( col1 ),
	Expr( __c2__ ), Name Expr( col2 )
);

/*alternative:
Substitute Into(	var,
	Expr( __c1__ ), Name Expr( col1 ),
	Expr( __c2__ ), Name Expr( col2 )
);*/

Eval( var );

topic Re: Some Insights on Dynamic Column Formulas in Discussions

Some Insights on Dynamic Column Formulas

Re: Some Insights on Dynamic Column Formulas

Re: Some Insights on Dynamic Column Formulas

Re: Some Insights on Dynamic Column Formulas