Putting a selected column name in a formula

[#1]

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

[#2]

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

[#3 · Accepted solution]

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

[#4]

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.

[#5]

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?

[#6]

Thank you, Mike,

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

[#7]

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

[#8]

OK. I will do so.

Thanks a bunch,

Kevin

[#9]

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