Subscribe Bookmark RSS Feed

Use selected column for formula in a new column

mkennke

Community Trekker

Joined:

Mar 18, 2016

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.

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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]) )
			)
		)
	)
);
Jim
4 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

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.

  1. Why are you creating Table Variables for the value of Range and the name of the new Column? 
  2. 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?
Jim
mkennke

Community Trekker

Joined:

Mar 18, 2016

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?

txnelson

Super User

Joined:

Jun 22, 2012

Solution

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]) )
			)
		)
	)
);
Jim
mkennke

Community Trekker

Joined:

Mar 18, 2016

Thank you!