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

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

pcarroll1 — Sat, 10 Jun 2023 23:14:35 GMT

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.

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

ih — Wed, 10 Jun 2020 19:55:23 GMT

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)

Mark_Bailey — Wed, 10 Jun 2020 19:30:50 GMT

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)

Mark_Bailey — Wed, 10 Jun 2020 19:41:24 GMT

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.

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

jerry_cooper — Thu, 11 Jun 2020 21:26:26 GMT

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.

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

jerry_cooper — Thu, 11 Jun 2020 22:26:19 GMT

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:

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.

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

pcarroll1 — Fri, 12 Jun 2020 14:48:44 GMT

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

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

pcarroll1 — Fri, 12 Jun 2020 14:58:12 GMT

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.