topic Re: Create/ add new columns using veribles in Discussions

Create/ add new columns using veribles

Stas — Thu, 02 Jan 2025 17:01:45 GMT

Hey guys, I apologize for the confusing title. I wasn't sure how to phrase it.
My question is: I have a code in which I'm adding new columns using long conditions.

The conditions are the same for all the columns, except to one parameter that changes.
Is there a way, instead writing each time the full code and the conditions with the new parameter value, create a template (like a function) that I will use a variable instead of the parameter to which I'll send each time the needed value for the conditions of the new column?

Re: Create/ add new columns using veribles

jthi — Thu, 02 Jan 2025 17:13:12 GMT

You can use functions or expressions for something like this

https://www.jmp.com/support/help/en/18.0/#page/jmp/advanced-expressions-macros-and-lists.shtml#ww339382

https://www.jmp.com/support/help/en/18.0/#page/jmp/advanced-functions.shtml#

Re: Create/ add new columns using veribles

hogi — Fri, 03 Jan 2025 08:26:36 GMT

Since JMP 18 the parallel creation of new columns got very easy - just have a look at Discussions/Formula/ - feature #3.

In short:
Select the columns which you want to use as input, then right click on a column header and click
new formula column /

As a last step, key in your formula and click on

... maybe, with a trick one can even use different parameters for the different columns?

Re: Create/ add new columns using veribles

hogi — Fri, 03 Jan 2025 07:44:18 GMT

If you want to use exactly the same formula again and again via the new formula right click menu, you can create your own "custom function" and enable the Transform Category option to make it show up in the menu.
After a restart the code has to be executed again, so include it in an AddIn or in your startup scripts.

Names Default To Here( 1 );
myAdd =
New Custom Function(
	"custom",
	"Add",
	Function( {x, y = 1},
		x + y - 1
	)
);
myAdd << Transform Category( 1 );

from scripting Index:

But this approach is very "static". To get the possibility to change a parameter, you have to vote here:

Re: Create/ add new columns using veribles

jthi — Fri, 03 Jan 2025 06:13:42 GMT

Here is one example of function which will add a number. It is missing some checks to be a "good" function but does provide the necessary idea that values which might change (column name and number to add in this case) must be evaluated (see Insert one expression into another... I mentioned earlier)

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

cols = {"height", "weight"};

add_num_to_col = Function({dt, colname, num_to_add}, {Default Local},
	new_col = Eval(EvalExpr(
		dt << New Column(colname || " add " || char(num_to_add), Numeric, Continuous, Formula(
			Expr(NameExpr(AsColumn(dt, colname))) + Expr(num_to_add)
		));
	));
	
	return(new_col);
);

col1 = add_num_to_col(dt, "height", 10);
col2 = add_num_to_col(dt, "height", 20);
col3 = add_num_to_col(dt, "weight", 5);

Write();

Sometimes using Eval(Substitute()) is cleaner and easier to understand solution and it could look something like this

add_num_to_col = Function({dt, colname, num_to_add}, {Default Local},
	new_col = Eval(Substitute(
		Expr(dt << New Column(colname || " add " || char(num_to_add), Numeric, Continuous, Formula(
			_colname_ + _num_to_add_
		))),
		Expr(_colname_), Name Expr(AsColumn(dt, colname)),
		Expr(_num_to_add_), num_to_add
	));
	
	return(new_col);
);

Re: Create/ add new columns using veribles

hogi — Fri, 03 Jan 2025 08:44:15 GMT

A combination of the previous approaches - with a little twist:

create a custom function *)
add it to a formula category
right click on a column header and click new formula column /
use the new formula
... and enter the parameter manually:

Add Custom Functions(
	New Custom Function(
		"custom",
		"add++",
		Function( {column, num_to_add},
			{Default Local},
			column,
			num_to_add
		),
		<<Formula Category( "NumberStuff" )
	)
);

The benefit: the user can enter specific parameters for every new column.

The disadvantage: no batch mode like in the previous posts.

kind of disadvantage: JMP doesn't resolve/replace the formula, it keeps it as add++(...).
To keep it alive the formula definition has to be executed again after a restart of JMP - and maybe: shared with colleagues.

Re: Create/ add new columns using veribles

Stas — Thu, 09 Jan 2025 03:33:40 GMT

Thank you everyone for the help.

I have a new related issue that perhaps someone can explain to me.

I used the following method to delete rows with no data in a specific column:
dt << select where( Is missing( Column( dt,col_name )[row()] ));

*As a result, it does delete the correct rows, but at the same time it deletes the values in the column. Can someone point me to the mistake?
Attaching here the script itself:

Re: Create/ add new columns using veribles

hogi — Thu, 09 Jan 2025 03:57:31 GMT

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

col_name="x";

New column(col_name); // maybe another column with the same name?
new_col = New column(col_name, Formula(if(:height>60,1 )));

// get the correct column name - uncomment to make it work
//col_name = new_col << get name();

dt << select where(  Is missing( Column( dt,col_name )[row()] ));
wait(1);
dt  << delete  rows;

Re: Create/ add new columns using veribles

hogi — Thu, 09 Jan 2025 04:04:44 GMT

I don't recommend using strings to refer to columns. It happens so often that the actual column is created by adding a "2" - and the string will lead to another column.

When you generate the column, you get a handle for the column. Just use this handle - and it will guide you to the right column:

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

col_name="x";
new_col = New column(col_name, Formula(if(:height>60,1 )));

dt << select where(  Is missing( new_Col[]  ));

Re: Create/ add new columns using veribles

hogi — Thu, 09 Jan 2025 04:07:26 GMT

I also don't like the [] to replace the column Column(col_name) with the current value As column(col_name).

:col can do both - and I learned from @jthi :

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

col_name="x";
new_col = New column(col_name, Formula(if(:height>60,1 )));

myCol = Name Expr(As Column(new_col)); // very cool trick (c) Jarmo
Show(Name Expr(myCol));
dt << select where(  Is missing( myCol  ));