BookmarkSubscribe
Choose Language Hide Translation Bar
mkennke
Community Trekker

Use selected column for formula in a new column

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.

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Use selected column for formula in a new column

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

View solution in original post

5 REPLIES 5
txnelson
Super User

Re: Use selected column for formula in a new column

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
0 Kudos
mkennke
Community Trekker

Re: Use selected column for formula in a new column

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?

0 Kudos
txnelson
Super User

Re: Use selected column for formula in a new column

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

View solution in original post

mkennke
Community Trekker

Re: Use selected column for formula in a new column

Thank you!

 

0 Kudos
Thomas1
Community Trekker

Re: Use selected column for formula in a new column

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

0 Kudos