cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Sop
Sop
Level III

How to remove inverted commas in a varible name in a formula

Hi,

I would like to choose two columns and do the formula. However, the variables in the formula have " " around the name and therefore is does not work. Aný suggestions?

 

dt = Current Data Table();
win = New Window( "Clean False Zeroes",
<<Modal,
Text Box( "Available Columns" ),
H List Box(
// list box to present cols
allcols = Col List Box( dt, all ), // or specify numeric instead of all
Lineup Box(
2,
bbx = Button Box( "X, Factor", xcols << append( allcols << get selected ) ),
// list box to receive cols
xcols = Col List Box( "Numeric", <<Modeling Type( {"Continuous"} ), min items( 1 ) ),
bby = Button Box( "Status, Response", ycols << append( allcols << get selected ) ),
// list box to receive cols
ycols = Col List Box( "Character", <<Modeling Type( {"Nominal"} ), min items( 1 ) )

),
V List Box(
bbOK = Button Box( "OK",
xvar = (xcols << Get Items);
yvar = (ycols << Get Items);
Show( xvar );

),
bbCancel = Button Box( "Cancel" )

)
)
);
// stop the script if user chooses Cancel
If( win["Button"] == -1,
Throw( "User Cancelled" )

);
 
FalseZeroes = Expr(
dt << New Column( "Temp", Numeric, "Continuous", Format( "Best", 12 ), formula( If( Is Missing( :Expr( yvar ) ), ., :Expr( xvar ) ) ) )

);
Eval( Eval Expr( FalseZeroes ) );

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Sop
Sop
Level III

Re: How to remove inverted commas in a varible name in a formula

Hey! It works if I use the nameexpr. Thank you both for you help. I am really happy.

View solution in original post

8 REPLIES 8
gianpaolo
Level IV

Re: How to remove inverted commas in a varible name in a formula

Hello Sop

try this code to the data table before to apply formulas

 

 

// code to replace any special JMP character with single char " : "
For(i = 1, i <= N Col(dt), i++,
Column(dt, i) << set name(Regex(Column(dt, i) << get name, "\W", ":", GLOBALREPLACE))

);

 

 

 

 

Gianpaolo Polsinelli
Craige_Hales
Super User

Re: How to remove inverted commas in a varible name in a formula

underscore might be a better choice than colon because the colon is an operator.

Craige
Sop
Sop
Level III

Re: How to remove inverted commas in a varible name in a formula

Hi gianpaolo_polsi,
Thanks for the script, I think I can use for other purposes, however, it does not solve my problem:If( Is Missing( {"Status (LOA)"} ),
.,
{"LOA (QC1007AI),%"}
)
I do not understand why I get these " " marks in my formula when I script it.
Craige_Hales
Super User

Re: How to remove inverted commas in a varible name in a formula

We'd need to see more of the JSL that is creating the script to give a better answer, but

 

{"LOA (QC1007AI),%"}

is a list of one quoted string. (The curly brackets make a list.) You can get the item out of the list and be a little closer

 

 

colNames = {"LOA (QC1007AI),%"};
theColumnName = colnames[1];
print(theColumnName); 

You'll need to make a column from the column name for most usage.

 

 

col = column( dt, theColumnName );

the column function will look up the name in the data table and return the column.

 

 

I think you may have used a function that returns the names of columns rather than a function that returns columns. There is a good post http://www.pega-analytics.co.uk/blog/column-references/ discussing some of these issues.

Craige
gianpaolo
Level IV

Re: How to remove inverted commas in a varible name in a formula

also based on Craige input(thanks!) i  arranged script using Column dialog editor

 

dt = Current Data Table();
 
cd=column Dialog(
Title(" Clean False Zeroes "),
xcols= ColList(" Numeric ", MinCol( 1 ), DataType("Numeric"), modeling type("Continuous") ),
ycols= ColList(" Character ", MinCol( 1 ), DataType("Character"), modeling type("Nominal") ),
);
 
 
xvar =cd["xcols"];
yvar =cd["ycols"];
 
xvar=xvar[1];
yvar=yvar[1];
dt << new column("check",, formula(If(Is Missing(yvar),.,xvar) ) );

 

Gianpaolo Polsinelli
Craige_Hales
Super User

Re: How to remove inverted commas in a varible name in a formula

I think you want to do something like this:

dt << new column("check",
formula(eval(evalexpr(If(Is Missing(expr(nameexpr(yvar))),.,expr(nameexpr(xvar))) )))
);

The selected names are in the scriptThe selected names are in the script

Without the extra work, you get something like this

 

The variable names are in the scriptThe variable names are in the script

Which only works as long as yvar holds :name and xvar holds :height. That will not be the case if you save the table, close and restart JMP, then open the table.

 

What does it do? 

eval() is a signal to formula to evaluate its argument. Normally you would not want that because normally the expression you give to formula() should not be evaluated until the column needs to run it. So the first code that runs is evalexpr() which will return a rewritten expression for the formula column. Evalexpr looks in its argument for expr() functions it can evaluate and replace with the result; there are two of them. So what gets evaluated is the NameExpr function, which returns the name of its argument...xval's argument and name is :height.

Craige
Sop
Sop
Level III

Re: How to remove inverted commas in a varible name in a formula

Thank you both for your suggestions, however, none of them works for me. For clarification, I have made a test file with the data in column A, Status in col B. Then I have made a third column with the formula included. You can ses that it works. If I run the script to do the same (I do not know which columns I want to clean for zeroes) you see in column 4 that it does not work well. Hope it all makes sense.

dt = Current Data Table();
win = New Window( "Clean False Zeroes",
	<<Modal,
	Text Box( "Available Columns" ),
	H List Box(
		// list box to present cols
		allcols = Col List Box( dt, all ), // or specify numeric instead of all
		Lineup Box(
			2,
			bbx = Button Box( "X, Factor", xcols << append( allcols << get selected ) ), 
			// list box to receive cols
			xcols = Col List Box( "Numeric", <<Modeling Type( {"Continuous"} ), min items( 1 ) ),
			bby = Button Box( "Status, Response", ycols << append( allcols << get selected ) ), 
			// list box to receive cols
			ycols = Col List Box( "Character", <<Modeling Type( {"Nominal"} ), min items( 1 ) )
		),
		V List Box(
			bbOK = Button Box( "OK",
				xvar = (xcols << Get Items);
				yvar = (ycols << Get Items);
				Show( xvar );
			),
			bbCancel = Button Box( "Cancel" )
		)
	)
);
// stop the script if user chooses Cancel
If( win["Button"] == -1,
	Throw( "User Cancelled" )
);



FalseZeroes = Expr(
	dt << New Column( "Temp", Numeric, "Continuous", Format( "Best", 12 ), formula( If( Is Missing( :Expr( yvar ) ), ., :Expr( xvar ) ) ) )
);
Eval( Eval Expr( FalseZeroes ) );
Sop
Sop
Level III

Re: How to remove inverted commas in a varible name in a formula

Hey! It works if I use the nameexpr. Thank you both for you help. I am really happy.