Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Use selected column for formula in a new column

Topic Options

- Start Article
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 7, 2017 1:43 AM
(2890 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- 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?

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Use selected column for formula in a new column

Thank you!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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?