825692_en-US

liliconcom

Create/ add new columns using veribles

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?

Automation and ScriptingBasic Data Analysis and ModelingRe: Create/ add new columns using veribles

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#

Insert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute 

Re: Create/ add new columns using veribles

No need to write any code.

Since JMP 18 this is super 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 


[I selected 128 columns, so create 128.
If you selected N columns, it will create N columns : )

 

 

 

Re: Create/ add new columns using veribles

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:
New Formula Column menu: user-Defined mini GUIs 

Re: Create/ add new columns using veribles

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

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=""></FORMULA>

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

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

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

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

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