topic Re: Select All Columns and Delete Formula Property? in Discussions

Select All Columns and Delete Formula Property?

aandw — Fri, 28 Oct 2016 13:16:41 GMT

Comparable to Copy All Paste Special Values in Excel, how can I select all columns in Data Table and delete column formulas while preserving the values of each column in JSL Script?

This previous discussion was a beneficial first step.

JSL - How to remove formula from column?

Re: Select All Columns and Delete Formula Property?

aniy — Fri, 29 May 2015 17:37:27 GMT

I have the same question. Currently, when I use <<delete property(formula) it removes the formula and the values. When I clear the formula manually by going to the column menu it does keep the values, but I need a script solution.

Re: Select All Columns and Delete Formula Property?

ms — Fri, 29 May 2015 17:57:36 GMT

Two approaches to "Paste Special" with jsl.

dt = Current Data Table();

// 1. Make a copy without formulas (and keep original table)

dt << Subset(Copy formula(0), All rows, Selected columns only(0));

// 2. loop through columns and delete any formulas

cols = dt << get column names();

For(i = 1, i <= N Col(dt), i++,

cols[i] << delete property(formula)

);

Re: Select All Columns and Delete Formula Property?

ms — Fri, 29 May 2015 18:03:13 GMT

That's strange. For me values are retained after running << Delete Property(Formula).

Currently on JMP 12 on Mac, but I am 99% certain it worked the same way in JMP 11.

Re: Select All Columns and Delete Formula Property?

Jeff_Perkinson — Fri, 29 May 2015 18:15:53 GMT

I just tested in JMP 9, JMP 10 and JMP 11 and the values were retained in all versions.

Can you test with this simple script and tell us what happens with the "Pred Formula ABRASION" column?


open("$SAMPLE_DATA\Tiretread.jmp");


column("Pred Formula ABRASION")<<delete property(formula);

-Jeff

Re: Select All Columns and Delete Formula Property?

Bill_Worley — Fri, 29 May 2015 18:39:44 GMT

One non-scripting option would be use Standardize Attributes. Select all of the columns where you want to remove the formula in Columns box on the left of the data table. Right click in the blue (highlighted) area and select Standardize Attributes. Go to Delete Properties at the bottom of the dialog box and select Column Properties. Select Formula and then click apply. The formulas should be removed and the values preserved.

Re: Select All Columns and Delete Formula Property?

aniy — Wed, 10 Jun 2015 19:17:52 GMT

Thank you for your reply, MS. Somehow I didn't get that notification and I am replying late.

The proposed solution: column(dt,1)<<delete formula; and column(dt,1)<<delete property (formula); worked eventually. I don't know what was wrong when I first tried it. I am using JMP 10 on Windows 7

Re: Select All Columns and Delete Formula Property?

robust1972 — Thu, 05 May 2016 13:29:02 GMT

this is exactly what I was looking for! Thanks!

Re: Select All Columns and Delete Formula Property?

ram — Mon, 03 Jul 2017 02:01:22 GMT

I am using delete property(Formula) in for loop and it does not delete formula. I am using JMP13. Below is

Try( column(datatable( dtname || "_Wide"), newcol) << Delete Formula ); << delete property(Formula);

Re: Select All Columns and Delete Formula Property?

DIVIDESbyZER0 — Thu, 27 Jul 2017 20:40:44 GMT

If you delete the formula in the same script you are creating the formula, you will need to use some kind of wait(1) function to allow the formula to calculate before deleting it.

New Column( "Name", Formula());
Wait(1);
:Name << Delete Formula;

Re: Select All Columns and Delete Formula Property?

Thomas1 — Wed, 20 May 2020 13:06:21 GMT

Short, simple and helpful. thanks