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
- :
- Creating new columns and inserting formula based on the original column cells

Topic Options

- 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

Jan 3, 2018 9:11 PM
(2844 views)

Hello,

I'm an inexperienced JMP scriptor and I'm trying to write my first larg(er) script. I'm having trouble with one part. Here's what I'm trying to do: I have various data tables that I will use this script on. I would like the script to look at all of the columns, excluding the first column (column and row numbers will vary for each data table) and create a new column for each column already present in the table. I would like the name of each of the new columns to be the same as each of the original columns but with a "_code" at the end, and preferably be positioned next to the original column in the table. Within each new column I would like the formula to be an if/then statement. If "Column A" has the text "<" in the cell, then 0, otherwise 1.

I hope my explanation makes sense. I've posted my script although it seems to break at line 8 and I'm not sure why.

Thank you for any help!

Lacey

```
Names Default To Here( 1 );
dt = Current Data Table();
col_list = dt << get column names( string );
For( i = 2, i <= nitems( col_list ), i++,
data_col = ":" || col_list[i];
code_col = col_list[i] || "_Code";
str = evalinsert (
"\[dt << New Colum ("^code_col^", Numeric, Continuous,
Format("Best",12),
Formula(If( Contains( "^code_col^", "<" ),
0,
1
)));
);]\");
eval(parse(str));
);
```

1 ACCEPTED SOLUTION

Accepted Solutions

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

Here is a modification of your code that I believe meets all of your needs. One item that I had to deal with, is that the columns that you start with (2-13) have to be character, so the formula does not error out. The Column 13 does error out the script, since it is a formula column that can not be changed to character, because as soon as it runs it's formula, it will force the column to be numeric.

```
Names Default To Here( 1 );
dt = Current Data Table();
col_list = dt << get column names( string );
For( i = 13, i <= N Items( col_list ), i++,
Eval(
Substitute(
Expr(
Column(col_list[i]) << Data Type( Character );
dt << Add Multiple Columns( __NewColName__, 1, after( __OrigCol__ ), Numeric );
Column( __NewColName__ ) << set formula( If( Contains( __OrigCol__, "<" ), 1, 0 ) );
),
Expr( __NewColName__ ), col_list[i] || "_Code",
Expr( __OrigCol__ ), Parse( ":" || col_list[i] )
)
)
);
```

Jim

5 REPLIES 5

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

Here is a modification of your code that I believe meets all of your needs. One item that I had to deal with, is that the columns that you start with (2-13) have to be character, so the formula does not error out. The Column 13 does error out the script, since it is a formula column that can not be changed to character, because as soon as it runs it's formula, it will force the column to be numeric.

```
Names Default To Here( 1 );
dt = Current Data Table();
col_list = dt << get column names( string );
For( i = 13, i <= N Items( col_list ), i++,
Eval(
Substitute(
Expr(
Column(col_list[i]) << Data Type( Character );
dt << Add Multiple Columns( __NewColName__, 1, after( __OrigCol__ ), Numeric );
Column( __NewColName__ ) << set formula( If( Contains( __OrigCol__, "<" ), 1, 0 ) );
),
Expr( __NewColName__ ), col_list[i] || "_Code",
Expr( __OrigCol__ ), Parse( ":" || col_list[i] )
)
)
);
```

Jim

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

Re: Creating new columns and inserting formula based on the original column cells

That worked great! Thanks!

It was my mistake that I left that "column 13" in the datatable. That was just me making sure my if then statement was working and I forgot to delete it when I posted it online. So I just deleted that out of my datatable, changed the 13 to a 2 in the wonderful script you sent and then it worked like a charm.

Thank you so much!

Lacey

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

Re: Creating new columns and inserting formula based on the original column cells

Hi Jim,

I went to try the script on a new data table and I ran into a problem with my column names. I deal with environmental data, and many times the analyte names (ie. column names) will have symbols in the name such as "-", "[", "]", "(", ")", and/or ",". When I ran the script it errored on the first column which was "3-Methyl-1-butene". Is there a fix for this?

Thank you,

Lacey

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

Re: Creating new columns and inserting formula based on the original column cells

The :Name() function is designed to handle this issue. Below is a modification I made to your script that I believe will solve the issue. I have not tested this modification, but I think it will work. If not, you should be able to see what I am attempting to do, and you should be able to get it to work.

```
Names Default To Here( 1 );
dt = Current Data Table();
col_list = dt << get column names( string );
For( i = 13, i <= N Items( col_list ), i++,
Eval(
Substitute(
Expr(
Column(col_list[i]) << Data Type( Character );
dt << Add Multiple Columns( __NewColName__, 1, after( __OrigCol__ ), Numeric );
Column( __NewColName__ ) << set formula( If( Contains( __OrigCol__, "<" ), 1, 0 ) );
),
Expr( __NewColName__ ), col_list[i] || "_Code" ,
Expr( __OrigCol__ ), Parse( ":Name(\!"" || col_list[i] || "\!:)" )
)
)
);
```

Jim

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

Re: Creating new columns and inserting formula based on the original column cells

Yes, I got that to work. Thank you so much!