topic Use selected column for formula in a new column in Discussions

Use selected column for formula in a new column

mkennke — Tue, 07 Feb 2017 09:43:23 GMT

Hi,

i try to genrate a script where i have to select a column . With this selection I create a new column, which contains a formula (with the selcted column). Here is my approach:

dt = Current Data Table ();
dt << New Table Variable( "Range", 2 );

New Window( "Select Column",
lb = Col List Box( all ), dt << New Table Variable( "Selected Column", lb <<get selected ),
Button Box( "Moving Average", dt <<New Column ("Movin Average", Numeric, Formula (:Range + "selected Column") )
));

I have two Problems:

1. How can I genrate a Coulumn Variable by the selection?

2. How can I use this Variable in my formula? For "Range" (numeric) it is possible, but for the selected column (character) not.

Re: Use selected column for formula in a new column

txnelson — Tue, 07 Feb 2017 16:15:49 GMT

Here is a working example of a script that will do what I believe your original script attempted to do.

dt = Open( "$SAMPLE_DATA\big class.jmp" );
dt << New Table Variable( "Range", 2 );

New Window( "Select Column",
	lb = Col List Box(
		Data Table( dt << get name ),
		all,
		min col( 1 ),
		max col( 1 ),
		dt << New Table Variable( "Selected Column", (lb << get selected )[1])
	),
	Button Box( "Moving Average",
		Eval(
			Substitute(
					Expr(
						dt << New Column( "Movin Average", Numeric, Formula( __formula__ ) )
					),
				Expr( __formula__ ), Parse( dt << get table variable( "Range" ) || " + :" || (lb << get selected)[1] )
			)
		)
	)
);

Now the issue I am having is to under stand a couple of items.

Why are you creating Table Variables for the value of Range and the name of the new Column?
Is the formula you specified actually what you want? Adding the number 2 to the value of another column, isn't giving any kind of Moving Average. Is what you really want to do, to create a true moving average of the current row, and the previous values from the last 2 rows?

Re: Use selected column for formula in a new column

mkennke — Tue, 07 Feb 2017 16:30:57 GMT

Thank You!

If I look to the code it should be exactly what I'm looking for. But i got an error message:

argument should be character in access or evaluation of 'Concat' , Bad Argument( (lb << get selected)[1] ), dt << get table variable( "Range" ) || /*###*/" + :" || /*###*/(lb << get selected
)[1] /*###*/

As Table variable I got the selcted column. But the evaluation of the formula for the new column is not possible.

Do I have to change any Preferences in my JMP Version?

Re: Use selected column for formula in a new column

txnelson — Tue, 07 Feb 2017 17:15:28 GMT

The code is complaining that the value of Range isn't a character string. So to correct that, the below code forces both the value of range, and the column name returned to be character strings, by using the char() function to insure that they are character strings.

dt = Open( "$SAMPLE_DATA\big class.jmp" );
dt << New Table Variable( "Range", 2 );

New Window( "Select Column",
	lb = Col List Box(
		Data Table( dt << get name ),
		all,
		min col( 1 ),
		max col( 1 ),
		dt << New Table Variable( "Selected Column", (lb << get selected )[1])
	),
	Button Box( "Moving Average",
		Eval(
			Substitute(
					Expr(
						dt << New Column( "Movin Average", Numeric, Formula( __formula__ ) )
					),
				Expr( __formula__ ), Parse( char(dt << get table variable( "Range" )) || " + :" || char((lb << get selected)[1]) )
			)
		)
	)
);

Re: Use selected column for formula in a new column

mkennke — Wed, 08 Feb 2017 10:09:45 GMT

Thank you!

Re: Use selected column for formula in a new column

Thomas1 — Thu, 03 Jan 2019 16:30:34 GMT

High Jim, this is a great script. Is it possible to create additional Formula columns (Col Mean() ...) by adding additional formula buttons?