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
- :
- How do I remove a column formula but retain the values (without scripting)

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

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

Feb 11, 2015 7:36 AM
(5513 views)

Hi, How do I remove a column formula but retain the values (without scripting)? I remember seeing an answer to this several months ago, maybe at the JMP summit, but I've forgotten and can't seem to figure it out.

Thanks,

Mike

1 ACCEPTED SOLUTION

Accepted Solutions

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

Feb 11, 2015 9:34 AM
(8563 views)
| Posted in reply to message from mikedriscoll 02/11/2015 10:36 AM

Just remove the Formula column property. Values will be retained (as I remember it...).

Right-click on the column header and pick Column Info. There should be a list of properties. Select Formula and click the remove button.

10 REPLIES

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

Feb 11, 2015 9:34 AM
(8564 views)
| Posted in reply to message from mikedriscoll 02/11/2015 10:36 AM

Just remove the Formula column property. Values will be retained (as I remember it...).

Right-click on the column header and pick Column Info. There should be a list of properties. Select Formula and click the remove button.

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

Re: How do I remove a column formula but retain the values (without scripting)

Thanks. That worked. I knew it was something simple, but couldn't remember it.

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

Re: How do I remove a column formula but retain the values (without scripting)

Can you answer this same question except using scripting? I need to use a formula on a column, get a value, but not store the formula.

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

Re: How do I remove a column formula but retain the values (without scripting)

`col << delete formula;`

Documentation for all of these kind of items is available at

Help==>Scripting Index

Jim

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

Re: How do I remove a column formula but retain the values (without scripting)

Or you could go without using a formula at all such as this:

```
dt<<New Column("Y",
<<Set Each Value(10+3*:x)
);
```

Of course that does depend on how complicated your formula is.

Dan Obermiller

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

Re: How do I remove a column formula but retain the values (without scripting)

This works:

dt<<);

Now: Does anyone know how to replace the "dots" in the column with "0's" without using a formula?

dt<<);

Now: Does anyone know how to replace the "dots" in the column with "0's" without using a formula?

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

Re: How do I remove a column formula but retain the values (without scripting)

I assume that when you are referring to "dots" that you are referencing the Missing Value character. Interactively, the easiest way to replace all of the Missing Values is to paste a "0" (zero) into all of the cells. The following steps will do that:

1. Go to one of the cells that has a missing value, and select the cell by clicking on it.

2. Enter into the cell a zero.

3. Copy the zero character into the paste buffer by selecting the cell and the right clicking and selecting "copy"

4. Go to a new cell for the same column that has a Missing Value and click on the cell.

5. Right click on the cell and select "Select Matching Cells"

6. Right click once again on the same cell, and select "Paste"

A zero "0" will then be pasted into all cells for that column that were Missing Values.

Or, you can use Recode

1. Click on the column header to select the column

2. Go to the pull down menu (or one of the column based red triangles) and select

Cols==>Recode

3. Change the Missing Value displayed to a zero

4. Click on "Done"

5. Select "In Place"

To do the same action using JSL, the following script will work:

```
Names Default To Here( 1 );
dt = Current Data Table();
columnVector = dt << get rows where( Is Missing( dt:your column name ) == 1 );
dt:your column name[columnVector] = 0;
```

Jim

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

Re: How do I remove a column formula but retain the values (with scripting)

I'm trying to delete a column in JMP. However, the column to be deleted is used by another column in a formula. I can't find the syntax to remove the formula when I delete the column. Any ideas how to accomplish this would be appreciated. I have tried several things with no success:

```
col << Go To( :PC50);
Column("PC50") << delete formula;
dt <
col << Go To( :PC50);
Col ("PC50") << delete formula;
dt <
col << Go To( :"PC50");
Col("PC50") << delete formula;
dt <
col << Go To( :PC50);
col(:PC50) << delete formula;
dt <
```

and on and on...

What am I missing?

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

Re: How do I remove a column formula but retain the values (with scripting)

Here is an example of how to delete a column that is being referenced in the formula of another column.

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\big class.jmp" );
dt << New Column( "LogHeight", formula( Log( :Height ) ) );
// Delete the formula in the column LogHeight
Column( "LogHeight" ) << delete formula;
dt << delete columns( "Height" );
```

Jim