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

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

Sop — Wed, 23 Jan 2019 20:01:22 GMT

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

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

gianpaolo — Wed, 23 Jan 2019 20:01:59 GMT

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

);

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

Craige_Hales — Tue, 22 Jan 2019 15:59:03 GMT

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

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

Sop — Wed, 23 Jan 2019 08:21:35 GMT

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.

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

Craige_Hales — Wed, 23 Jan 2019 11:40:10 GMT

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.

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

gianpaolo — Wed, 23 Jan 2019 20:04:41 GMT

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

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

Craige_Hales — Wed, 23 Jan 2019 19:59:23 GMT

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 script

Without the extra work, you get something like this

The 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.

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

Sop — Thu, 24 Jan 2019 12:03:50 GMT

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

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

Sop — Thu, 24 Jan 2019 12:09:40 GMT

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