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

- JMP User Community
- :
- Discussions
- :
- Apply same formula to several columns

- 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

Jun 22, 2019 7:48 PM
(4322 views)

I have about 200 columns, and I want to apply the same formula to almost all of these columns.

The formula I want to apply is the square root of “Range 0 to 1 + 1” to each colum. I don’t want to write the name of the columns in a script because they are many.

Could anyone help me to figure out a script for this purpose?

Thank you!

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

Created:
Jun 23, 2019 11:56 AM
| Last Modified: Jun 23, 2019 12:00 PM
(4277 views)
| Posted in reply to message from bordini 06-22-2019

Hi @bordini,

If you're okay with an interactive solution, using Cols > Standardize Attributes will allow you to standardize the formula across your 200 new columns, and will, importantly, allow you to substitute the column references used in the formula. Here's a link to the documentation that has an example of standardizing a formula.

In your case, you can also take advantage of JMP's Instant formulas to make the initial Range columns. Then, use Cols > Standardize Attributes (or right click column header > standardize attributes) to add the +1 to each column, while preserving the unique column references in each formula column (which is what Substitute Column Reference will do). See the video below for a quick demonstration.

I hope this helps!

//Edit: I missed the square root part. You can add that when you're editing the formula. Just select the outermost formula container and click the root button in the toolbar:

9 REPLIES 9

Highlighted
##

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

Re: Apply same formula to several columns

I am a little confused about what you are asking for. Do you want the same calculation performed on the 200 columns, or do you want to create 200 new columns and apply a formula to the new column?

Additionally, this might help. You can easily get the names of the columns in a data table by using the "<< get column names" message and apply it to your data table

`colNamesList = dt << get column names(string, numeric);`

The above code will create a list called colNamesList and will place in it the quoted name of each of the numeric column names. The "string" qualifier, will make each element in the members of the colNamesList, string variables. You can then loop through the columns and generate the calculations you need for each of the columns in the list.

This is all documented in the Scripting Guide

Help==>Books==>Scripting Guide

I suggest you take the time to read through the guide.

Jim

Highlighted
##

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

Re: Apply same formula to several columns

@txnelson yes, I want the same calculation applied to all 200 columns (which are all the continuous columns I have). I want to apply the same formula to all of them. It would be okay to create new columns with the formula applied to them, or to apply the formula directly to the columns. The formula is the square root of (Range 0 to 1 +1).

Thanks for sharing that scrip with me, but it didn’t run. Do I need to do anything special to run this script? I copied and pasted it into the Script window, and hit “run”.

I’m sorry if my questions are too obvious. I don’t know anything about scripts.

Thank you,

Isadora

Highlighted
##

Right click the column with your formula and choose "Copy Column Properties". Select all your columns to which the formula should be applied. Right click and choose "Paste Multiple Columns Properties".

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

Re: Apply same formula to several columns

Highlighted
##

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

Re: Apply same formula to several columns

Thank you!

Highlighted

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

Created:
Jun 23, 2019 11:56 AM
| Last Modified: Jun 23, 2019 12:00 PM
(4278 views)
| Posted in reply to message from bordini 06-22-2019

Hi @bordini,

If you're okay with an interactive solution, using Cols > Standardize Attributes will allow you to standardize the formula across your 200 new columns, and will, importantly, allow you to substitute the column references used in the formula. Here's a link to the documentation that has an example of standardizing a formula.

In your case, you can also take advantage of JMP's Instant formulas to make the initial Range columns. Then, use Cols > Standardize Attributes (or right click column header > standardize attributes) to add the +1 to each column, while preserving the unique column references in each formula column (which is what Substitute Column Reference will do). See the video below for a quick demonstration.

I hope this helps!

//Edit: I missed the square root part. You can add that when you're editing the formula. Just select the outermost formula container and click the root button in the toolbar:

Highlighted
##

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

Re: Apply same formula to several columns

Highlighted
##

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

Re: Apply same formula to several columns

Hi,

I also have similar issue. I want to calculate natural logarithm on 10 different columns. But I want to see these calculations on new columns. For example as shown in jmp file. There are 3 columns. I want to calculate their natural logarith on 3 new columns. How can I make it easily. 3 column not a big size. But when you have 20 column it is difficult to apply the same formula for 20 column.

could you please help on this?

Highlighted
##

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

Re: Apply same formula to several columns

Created:
May 4, 2020 3:29 AM
| Last Modified: May 4, 2020 3:32 AM
(1397 views)
| Posted in reply to message from nac 05-04-2020

This can be handled in the Standardize Columns Attributes

- Create a new column and enter in the formula you want to use
- Create the number of new columns required, that you are going to want to replicate the formula for
- Select all of the columns, including the first column that already has the formula, and then from the red triangle in the columns panel, select, Standardize Attributes
- In the Standardize Attributes dialog window, click on the down arrow for Column Properties and select Formula.
- In the expanded window, with the Formula information in it, select the Check Box, "Substitute Column Reference"
- Click on OK and the new Formulas will be created

Jim

Highlighted
##

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

Re: Apply same formula to several columns

Select all the original data columns. Right-click on the name of one of them and select **New Formula Column > Transform > Log.**

Learn it once, use it forever!

Article Labels

There are no labels assigned to this post.