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
aandw
Level II

Select All Columns and Delete Formula Property?

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Select All Columns and Delete Formula Property?

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)

);

View solution in original post

10 REPLIES 10
aniy
Level I

Re: Select All Columns and Delete Formula Property?

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.

ms
Super User (Alumni) ms
Super User (Alumni)

Re: Select All Columns and Delete Formula Property?

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.

Jeff_Perkinson
Community Manager Community Manager

Re: Select All Columns and Delete Formula Property?

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

-Jeff
ram
ram
Level IV

Re: Select All Columns and Delete Formula Property?

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);

aniy
Level I

Re: Select All Columns and Delete Formula Property?

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

DIVIDESbyZER0
Level III

Re: Select All Columns and Delete Formula Property?

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;

 

ms
Super User (Alumni) ms
Super User (Alumni)

Re: Select All Columns and Delete Formula Property?

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)

);

robust1972
Level IV

Re: Select All Columns and Delete Formula Property?

this is exactly what I was looking for! Thanks!

Thomas1
Level V

Re: Select All Columns and Delete Formula Property?

Short, simple and helpful. thanks