topic Putting a selected column name in a formula in Discussions

Putting a selected column name in a formula

kevinatkodak — Fri, 15 Jun 2018 18:04:02 GMT

Re: Putting a selected column name in a formula

kevinatkodak — Fri, 15 Jun 2018 18:08:11 GMT

I'm trying to figure out how to get my column selection from a dialog box into a formula properly. I looked at lots of discussions and samples from the scripting book, tried a bunch of things. No dice yet. As you can see in the script, I want to select a column and make a new column based on the column moving average formula. I would appreciate help.

Here is my latest try:

dt = Current Data Table();
colname = .;

New Window( "Select Column",
	x = Col List Box(
		Data Table( dt << get name ),
		all,
		min col( 1 ),
		max col( 1 ),
		colname = Eval Expr (Expr(x << get selected)[1]),
		dt << New Table Variable( "Selected Column", (x << get selected)[1] )
	),
	
	
	Button Box( "Moving Average",
		dt << New Column( Char( (x << get selected)[1] ) || " Movin Average",
			Numeric,
			Formula( Col Moving Average( Name Expr(colname), 0, 4, 0 ) )
		)
	)
);

Re: Putting a selected column name in a formula

mikedriscoll — Fri, 15 Jun 2018 19:08:12 GMT

Try this. The last two lines of the col list box() part appeared to be a script, which means they should have been separated by the semicolon (or glue). The rest of those lines are arguments of the col list box, separated by commas. The "colName = ... " part of the col list box is commented out here because I don't think it was doing much. Better to grab it during the button box part of the script. You can add back the table variable if you want it.

It would be better to make the list box load numeric columns only. I couldn't remember how to do that correctly. Presently it loads all into the list box. I changed min / max col() to max selected. I.e. no more than 1 at a time.

As for the formula part, it usually takes me a little trial and error to get strings where I want them. This seems to work.

dt = Current Data Table();
colname = .;

New Window( "Select Column",
	x = Col List Box(
		Data Table( dt << get name ),
		all,//numeric,
		//min col( 1 ),
		max selected( 1 )//,
		//colname = Eval Expr (Expr(x << get selected)[1])//,
		//dt << New Table Variable( "Selected Column", (x << get selected)[1] )
	),
	
	
	Button Box( "Moving Average",
		colName = Char( (x << get selected)[1] );
		//myCol = column(colName);
		
		formulaString = "Col Moving Average( :"|| char(colName) ||", 0, 4, 0 )";
		
		dt << New Column( colName  || " Movin Average",
			Numeric,
			//Formula( Col Moving Average( Name Expr(colname), 0, 4, 0 ) )
			Formula( eval(parse(formulaString)) )
		)
	)
);

Re: Putting a selected column name in a formula

gzmorgan0 — Fri, 15 Jun 2018 21:40:17 GMT

Your script will work only if there are no special symbols in your column names. If there are then the named reference must be :Name("string"). Also, a check that a column was selected was added to the script.

Names Default to Here(1);

dt = Current Data Table();
colname = .;

New Window( "Select Column",
	x = Col List Box(
		Data Table( dt << get name ),
		all, <<Set Data Type("Numeric"),
		Max Selected(1)
	),
	
	
	Button Box( "Moving Average",
	  If(NItems(x << get selected)==0, 
		   Caption("No columns are selected "); Wait(3); Caption(Remove)
		, //else
		colName = (x << get selected)[1]; 
		formulaString = EvalInsert("\[Col Moving Average( :Name("^colName^"), 0, 4, 0 )]\");
		
		dt << New Column( colName  || " Movin Average",
			Numeric,
			//Formula( Col Moving Average( Name Expr(colname), 0, 4, 0 ) )
			Formula( eval(parse(formulaString)) )
		); //end New Column
	  ) //end If
	) //end ButtonBox
); //New Window

Re: Putting a selected column name in a formula

kevinatkodak — Mon, 18 Jun 2018 16:48:14 GMT

Thank you, Mike,

Yup, it did indeed work for me. And, I learned some more scripting technique.

Re: Putting a selected column name in a formula

kevinatkodak — Mon, 18 Jun 2018 16:50:33 GMT

Thank you, GZ,

Yes, your solution worked for me too. Trapping if no column is selected is a good tip. I will use it going forward.

Re: Putting a selected column name in a formula

mikedriscoll — Mon, 18 Jun 2018 18:05:18 GMT

No problem. But I would use gz's method. It is more robust in that it handles columns with special characters in the name.

Re: Putting a selected column name in a formula

kevinatkodak — Mon, 18 Jun 2018 18:12:16 GMT

OK. I will do so.

Thanks a bunch,

Kevin

Re: Putting a selected column name in a formula

kevinatkodak — Wed, 14 Nov 2018 22:25:21 GMT

Continuing my effort. I've made a lot of progress. I put a selected column name in my formula and can now make an overlay graph of the selected column with its Moving Average using MA parameters from a dialog box. Nice. Very nice. I'm using the "Overlay Plot" function. I can modify all the points on both graphs, but cannot figure out how to modify just one of the plot's attributes.

Here's a code snippet to illustrate. This works:

cn1 = colName || "";
cn2 = colName || " Moving Average";
dt << New Column( colName || " Moving Average",
	Numeric,
	Formula( Eval( Parse( formulaString ) ) )
);
oc = Column( cn1 );
nc = Column( cn2 );
nw2 << Close Window;
 
funny = Overlay Plot( Y( oc, nc ), );
funny << (Connect Points( 1 ));

The resulting plot has both the MA and original points connected.

However, if I change the last line to modify just one of the plots, say, this, it doesn't work:

funny << oc( Connect Points( 1 ) );

No points are connected.

So, "oc" is recognized in the "Overlay Plot" function to be one of the Y columns, but not recognized in the above statement.

Can someone help please? My mental health is at stake.:) I've attached a data table with my whole script embedded. There is an additional small auto-generated script to make the plot the way I'd like the whole script to make it; I could only make this plot by manually changing the legend.

Kevin

Re: Putting a selected column name in a formula

gzmorgan0 — Thu, 15 Nov 2018 02:16:24 GMT

First a couple notes:

oc is a column reference the syntax oc( Connect Points(1) ) could not be interpretted by JMP.
It seems the syntax shown in the Scripting Index for the Overlay Plot requires the column name, see example below. It will also accept :Name("Volume")(Connect Color (0) );

obj = Overlay Plot(
	X( :Date ),
	Y( :High, :Low, :Close, :Volume ),
	Y Scale( Left, Left, Left, Right )
);
obj << Connect Points( 1 );
obj << :Volume( Connect Color( 0 ) );
obj << :Low( Connect Color( "Red" ) );

The overlay plot has some nice features, however, JMP developers would probably recommend using Graph Builder. For the structure of your data table, Overlay might be easier to use, so I will use Overlay.
The script below started with a subset of the Football data table, none of th ecolumns you added. Then computes teh Moving Average. The script creates an overlay plot and uses Eval(Parse(EvalInsert())) to construct the syntax shown in the Scripting Index with the quoted name and option to turn off points.
The second part of the script demonstartes an alternate method. Turn on lines and points for all overlay columns. Then use Xpath() to create a list of references to the line segments and the marker segments. To turn off a line, set the line width to 0. To turn off points, set the transparency to 0. I find working with the segments uses simpler syntax. However, Xpath() while extremely useful, is probably consisdered more advanced. Hope that helps.

Names default to here(1);

dt = current data table();

colnme = "Speed";

oc = Column(dt, colnme);
nc = dt << New Column( colnme || " Moving Average",

     Numeric,
     Formula(Col Moving Average(oc[],1,4,16) )
     
 );
 
 ovp = Overlay Plot( Y(oc, nc));
 ovp << Connect Thru Missing(1);
 ncnme = nc << get name;
 
 //The syntax is ovp << :col( opt1);
Eval( Parse(EvalInsert("ovp <<  :name(\!"^ncnme^\!")(Show Points(0))" ) ) ) ;

//alternate method 

 ovl = Overlay Plot( Y(oc, nc));
 ovl << Connect Thru Missing(1);

lines  = ovl << Xpath("//LineSeg");
points = ovl << Xpath("//MarkerSeg");

wait(2); 
lines[1] << Line width(0); //turn off line 1
wait(2);
lines[1] << Line width(2); //turn on line 1
wait(2);
lines[1] << Line width(0); //turn off line 1
wait(2);
points[2] << Transparency(0); //turn off points 2

Re: Putting a selected column name in a formula

kevinatkodak — Thu, 15 Nov 2018 13:51:52 GMT

gzmorgan,

You are a wizard. I tried both methods that you illustrated with success. More importantly, I learned more scripting technique. Many thanks.

Re: Putting a selected column name in a formula

Thomas1 — Wed, 09 Jan 2019 19:45:36 GMT

Hi gzmorgan0,

this is a great script. How must the script chnaged in order to add other formula buttons, like Col Mean or Col Std Dev?