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

- JMP User Community
- :
- Discussions
- :
- Re: call a column by referring to variable

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

Highlighted

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

Created:
Jun 12, 2020 12:20 AM
| Last Modified: Jun 12, 2020 4:12 AM
(616 views)

A complete newbie here,

could not find the solution in older posts,

I have created a variable containing an existing column name. I want to create a new column, were the data from two other columns is summed (calling the columns by variables). the usage of variable is for later use in a for loop.

Thanks for helping!

for the below code:

```
dt = Current Data Table();
columnname1 = Column( dt, 1 ) << Get Name;
columnname2 = Column( dt, 2 ) << Get Name;
dt << New Column( "new_" || columnname1, Numeric , continuous, formula(column(dt, columnname1)+column(dt, columnname2)));
```

I get the following error:

Column new_Column 1 2 Formula Interrupted

Cannot convert argument to a number [or matrix] 1 times At rows: 2 Operation: Add, Column( dt, columnname1 ) + /*###*/Column( dt, columnname2 ) /*###*/

Formula evaluation errors have been ignored

Column( "new_Column 1 2" )

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

You've already created variables that reference the columns. You can use those in your formula, but you need to use the AsColumn function:

`formula( AsColumn(columnname1) + AsColumn(columnname2) )`

-Dave

4 REPLIES 4

Highlighted

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

You've already created variables that reference the columns. You can use those in your formula, but you need to use the AsColumn function:

`formula( AsColumn(columnname1) + AsColumn(columnname2) )`

-Dave

Highlighted
##

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

Re: call a column by referring to variable

You can get it to work, with

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
columnname1 = Column( dt, 4 ) << Get Name;
columnname2 = Column( dt, 5 ) << Get Name;
dt << New Column( "new_" || columnname1,
Numeric,
continuous,
formula(
As Column( dt, columnname1 ) + As Column( dt, columnname2 )
)
);
```

However, there is a potential problem with this. The stored formula is:

This formula will continue to work, as long as your JMP session

- Has the variables, dt, columnname1 and columnname2 defined. What happens if you save the data table, shutdown JMP, and then tomorrow you come in and startup JMP, and open the data table. The formula will not find those variables, and the formula will fail.
- Within the session, the variables, dt, columnname1 and columnname2, do not change their value. So, if you create this column, and later on in your script, you happen to change the value of variable columnname1, since formula columns in a JMP data table are dynamic, the formula will rerun when changes to the data table take place, JMP will use the new value of the variable, and will create new values for the formula column.

What you really need to do, is to use a method that creates the actual JSL syntax for the formula. In the above example, what you really want is for the formula to be

`:height + :weight`

The JSL below is one way to do this. It uses the Substitute() function to implant the actual column names into the formula.

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
columnname1 = Column( dt, 4 ) << Get Name;
columnname2 = Column( dt, 5 ) << Get Name;
Eval(
Substitute(
Expr(
dt << New Column( "new_" || columnname1,
Numeric,
continuous,
formula( __c1__ + __c2__ )
)
),
Expr( __c1__ ), Parse( ":" || columnname1 ),
Expr( __c2__ ), Parse( ":" || columnname2 )
)
);
```

Jim

Highlighted
##

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

Re: call a column by referring to variable

thank you for your quick reply. it works!

Highlighted
##

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

Re: call a column by referring to variable

@txnelson is quite right about this. The method I suggested was a lazy option and in hindsight not an approach I would recommend. If you just want a column with correct values you can do a calculation in a loop and not bother with a formula; if the formula is important to you then you need to check that your script has correctly encoded the formula - it's not sufficient for the numbers to appear correct. Script variables will not persist beyond the lifetime of the script, so the formula needs to correctly include column references - this is achieved by using expression-substitution techniques such as the one illustrated by Jim.

-Dave