cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
pcarroll1
Level IV

Copying Formula columns from one table to another (Column name included)

I often need to copy a series of formula columns from one table to another.  Although "Copy Multi Column Properties" makes that job easier, it does not include the column name.  As my column names can be quite long, it is time consuming to do this, even using cut and paste, since there can be many of these columns.  Is there an easy method to copy the properties (formula, name and all) from one table to another?  I realize that I can write a script to do this, but thought maybe I was unaware of some option.

1 ACCEPTED SOLUTION

Accepted Solutions
jerry_cooper
Staff (Retired)

Re: Copying Formula columns from one table to another (Column name included)

Sorry @pcarroll1 ,

It just dawned on me that you're probably not using version 15 - I believe this is a new feature. 

 

You can do what you want in previous versions, although, as you mentioned, the column names do not convey. However, there is a method for copying multiple column names from one table to another.

 

After you've pasted the formula columns into the new table (with "Paste Multi Column Properties"), go back to your source table (be sure that your formula columns are still selected), then click on the header bar in the "Columns" panel:

jerry_cooper_1-1591914257273.png

Type Ctrl-C to copy the column names.  Then go back to the destination table and ensure that your new formula columns are still selected, click on the Columns header and then type Ctrl-V to paste the column names. Obviously, you'll need to make sure that you haven't changed the order of the columns in either table before doing this, otherwise, your columns won't be named correctly.  It's a couple of extra steps, but not as daunting as re-typing a bunch of long names. 

View solution in original post

7 REPLIES 7
ih
Super User (Alumni) ih
Super User (Alumni)

Re: Copying Formula columns from one table to another (Column name included)

I have the script below saved in the Cols menu using in add-in and use it all the time, both when writing scripts to recreate what I did visually and to copy columns from one table to another.  Select columns in your source table, run this script, select your target data table, open a new script window, paste what was saved to the clipboard and hit run.

 

Names default to here(1);
ColumnList = Current Data Table() << Get Selected Columns();
ScriptOut = "";
for( i = 1, i <= n items(ColumnList), i++,
	ThisScript = Char(Column( Current Data Table(), ColumnList[i] ) << Get Script) || ";";
	ThisScript = substitute( ThisScript, ", Set Selected", "" );
	ScriptOut = ScriptOut || If( i > 1, "\!N", "" ) || ThisScript;
);

Set clipboard( ScriptOut );

Example code saved to the clipboard:

New Column("Sepal length Lagged", Numeric, "Continuous", Format("Best", 10), Formula(Lag(As Column("Sepal length"), 1)));
New Column("Sepal width Lagged", Numeric, "Continuous", Format("Best", 10), Formula(Lag(As Column("Sepal width"), 1)));
New Column("Petal length Lagged", Numeric, "Continuous", Format("Best", 10), Formula(Lag(As Column("Petal length"), 1)));
New Column("Petal width Lagged", Numeric, "Continuous", Format("Best", 10), Formula(Lag(As Column("Petal width"), 1)));

Re: Copying Formula columns from one table to another (Column name included)

There is something that you are not aware of!

 

  • Open the source and the destination data tables.
  • Select the original columns in the first data table.
  • Press and hold the Control key with Windows or the Command key with Macintosh.
  • Drag the original columns from the source data table to the destination data table.

Re: Copying Formula columns from one table to another (Column name included)

OK, I rushed that one!

 

This operation does not carry column properties over to the destination. Only the data values and column attributes.

 

A simple drag (without modifier key) will move the columns from one table to another instead of copying them.

 

You can also right-click on the selected data columns in the source data table and select copy column properties or copy columns, but this way does not copy the values!

 

So I guess there is no simple way to get what you want in one step. If you have many columns to re-use, then the script might be the best way. I was trying to illustrate that there is a lot of capability through the UI.

jerry_cooper
Staff (Retired)

Re: Copying Formula columns from one table to another (Column name included)

Hi @pcarroll1 ,

If you select the formula columns you want to copy from the source table, right-click the header of one of them and select "Copy Columns"; then right-click the header of the first empty column to the right of your destination table data and select "Paste Columns" - I think you'll get what you're after.  BTW, right-clicking an existing column in the destination table will insert the new formula columns immediately after that column.

jerry_cooper
Staff (Retired)

Re: Copying Formula columns from one table to another (Column name included)

Sorry @pcarroll1 ,

It just dawned on me that you're probably not using version 15 - I believe this is a new feature. 

 

You can do what you want in previous versions, although, as you mentioned, the column names do not convey. However, there is a method for copying multiple column names from one table to another.

 

After you've pasted the formula columns into the new table (with "Paste Multi Column Properties"), go back to your source table (be sure that your formula columns are still selected), then click on the header bar in the "Columns" panel:

jerry_cooper_1-1591914257273.png

Type Ctrl-C to copy the column names.  Then go back to the destination table and ensure that your new formula columns are still selected, click on the Columns header and then type Ctrl-V to paste the column names. Obviously, you'll need to make sure that you haven't changed the order of the columns in either table before doing this, otherwise, your columns won't be named correctly.  It's a couple of extra steps, but not as daunting as re-typing a bunch of long names. 

pcarroll1
Level IV

Re: Copying Formula columns from one table to another (Column name included)

jerry_cooper,

    This worked great.  I did not know about copying names from the Column Panel.  

    You're right that I don't yet have JMP15 but hope to get it after my companies merger goes through.

Pat

 

pcarroll1
Level IV

Re: Copying Formula columns from one table to another (Column name included)

I should have also added this little tidbit. Since some of my formulas referred to some of the other new column names, those formulas failed when I used the order that you suggested. All the column formulas worked when I first copied the names and then the formulas.