Choose Language Hide Translation Bar
Highlighted
bordini
Level III

Apply same formula to several columns

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
julian
Community Manager Community Manager

Re: Apply same formula to several columns

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!

@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:root.gif

 

 

View solution in original post

9 REPLIES 9
Highlighted
txnelson
Super User

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
bordini
Level III

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
Thomas1
Level V

Re: Apply same formula to several columns

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

 

 

 

Highlighted
bordini
Level III

Re: Apply same formula to several columns

Thank you!
Highlighted
julian
Community Manager Community Manager

Re: Apply same formula to several columns

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!

@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:root.gif

 

 

View solution in original post

Highlighted
bordini
Level III

Re: Apply same formula to several columns

Hi @julian ,

 

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

Highlighted
nac
nac
Level III

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
txnelson
Super User

Re: Apply same formula to several columns

This can be handled in the Standardize Columns Attributes

  1. Create a new column and enter in the formula you want to usecopy1.PNG
  2. Create the number of new columns required, that you are going to want to replicate the formula forcopy2.PNG
  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 Attributescopy3.PNG
  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"                                      copy4.PNG
  6. Click on OK and the new Formulas will be createdcopy5.PNG
Jim
Highlighted

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.