Apply same formula to several columns

[#1]

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!

[#2]

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.  

[#3]

@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

[#4]

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".

[#5]

Thank you!

[#6 · Accepted solution]

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. 

Standardize Attributes

Video Player is loading.

Play Video

Play

Mute

Current Time 0:00

/

Duration 0:33

Loaded: 0%

0:00

Stream Type LIVE

Seek to live, currently behind liveLIVE

Remaining Time -0:33

1x

Playback Rate

Chapters

• Chapters

Descriptions

• descriptions off, selected

Captions

• captions settings, opens captions settings dialog
• captions off, selected

Audio Track

• en (Main), selected

Picture-in-PictureFullscreen

This is a modal window.

Beginning of dialog window. Escape will cancel and close the window.

Text

ColorWhiteBlackRedGreenBlueYellowMagentaCyanTransparencyOpaqueSemi-Transparent

Background

ColorBlackWhiteRedGreenBlueYellowMagentaCyanTransparencyOpaqueSemi-TransparentTransparent

Window

ColorBlackWhiteRedGreenBlueYellowMagentaCyanTransparencyTransparentSemi-TransparentOpaque

Font Size

50%75%100%125%150%175%200%300%400%

Text Edge Style

NoneRaisedDepressedUniformDropshadow

Font Family

Proportional Sans-SerifMonospace Sans-SerifProportional SerifMonospace SerifCasualScriptSmall Caps

Reset restore all settings to the default valuesDone

Close Modal Dialog

End of dialog window.

Close Modal Dialog

This is a modal window. This modal can be closed by pressing the Escape key or activating the close button.

I hope this helps!

@julian 

//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:

[#7]

Hi @julian ,

That’s exactly what I needed! The video was very helpful! Thank you very much!

[#8]

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?

[#9]

This can be handled in the Standardize Columns Attributes

1. Create a new column and enter in the formula you want to use
2. Create the number of new columns required, that you are going to want to replicate the formula for
3. 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
4. In the Standardize Attributes dialog window, click on the down arrow for Column Properties and select Formula.
5. In the expanded window, with the Formula information in it, select the Check Box, "Substitute Column Reference"                                     
6. Click on OK and the new Formulas will be created

[#10]

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