topic Delete Columns with Formula Reference in JSL in Discussions

Delete Columns with Formula Reference in JSL

robot — Wed, 07 Mar 2018 14:08:17 GMT

Hi,

I have a script where I would like to delete columns that contain a formula reference. This is easy to do in JMP: when delete the unnecessary columns I get a "JMP Alert" and select "Remove Formula". I am then able to delete my metadata columns, but the table maintains the result calculated in the formula column.

My problem is: how can I script this? Is there an option to delete a metadata column that contains a formula reference? I am using JMP10. Please see the example below.

/*
bigClass = Open( "$SAMPLE_DATA/Big Class.jmp" );
bigClass << New Column( "height / weigh", Numeric, Continuous, Format( "Best", 12 ), Formula( :height / :weight ) );
bigClass << Delete Column( "height" );
bigClass << Delete Column( "weight" );
*/

Thanks!

Re: Delete Columns with Formula Reference in JSL

robot — Wed, 07 Mar 2018 14:10:29 GMT

Never mind. I found the answer:

https://community.jmp.com/message/48129#48129

Re: Delete Columns with Formula Reference in JSL

DonHil — Wed, 07 Mar 2018 13:21:38 GMT

Can you tell me how you solved this problem? I have tried to remove formula, but this removed the values in the original column. I need to delete a column contains a column reference. Thank you.

Re: Delete Columns with Formula Reference in JSL

powerpuff — Tue, 03 Apr 2018 19:01:34 GMT

@DonHil Column("column name") << delete formula;

Re: Delete Columns with Formula Reference in JSL

nathan-clark — Fri, 19 Jun 2020 20:22:06 GMT

This is kind of old, but I have an addition to this and I figure I'll try here first :)

What if I don't know the columns with formula that will be referencing the column i want to delete. I am trying to make something dynamic, and I would like to precode it so it wilkl automatically delete the formula if there is a conflict (just like if i did it manually), but I can't find any syntax that allows for that.

the "easiest" method is to just remove all formulas so it's never an issue, but that seems a bit more extreme than it needs to be.

Re: Delete Columns with Formula Reference in JSL

rbfinner — Fri, 11 Mar 2022 20:46:55 GMT

Hi Nathan,

Did you find a solution to your posted question/problem?

I'm trying to do the same... I'd like to delete a column that is used in a formula in one or more other columns.

I want to make it dynamic rather than specifying a "col << delete formula" statement for a particular column.

Re: Delete Columns with Formula Reference in JSL

nathan-clark — Wed, 16 Mar 2022 13:11:25 GMT

I have not found a solution yet. The most painful to code that I've come up with would be to essentially grab all the formulas and create a map of formula column to the local variables necessary for that formula. That could then be used dynamically when column deletions need to be done.

I assume something like that is the basis for how JMP knows to trigger the warning in the first place. It would take me some time to come up with something clean for the above, so ideally, JMP could create a JSL function to give us a boolean capability or something :) I will probably end up chatting with JMP support for a more official solution (or ask for one in the wish list).

Re: Delete Columns with Formula Reference in JSL

nathan-clark — Wed, 16 Mar 2022 17:20:48 GMT

Turns out I did already submit something of this nature to the JMP wish list, and was in touch with JMP support (no clear solution as of yet, but a workaround I haven't had time to integrate quite yet).
Wish List item:

A couple work around options from my chats with JMP support.

Names Default to here(1);
dt = Open( "$SAMPLE_DATA\Big Class.jmp" );
dt << New Column( "Test 1", Numeric, Formula( :height + :weight ) );
dt << New Column( "Test 2", Numeric, Formula( :height + :age ) );
 
keepCols = AssociativeArray({"name","Test 1","sex"});
 
formList = {};
formCols = {};
for(i=1,i<=ncol(dt),i++,
       if(IsEmpty(Column(dt,i) << get formula),
              Empty()
              ,
              insertInto(formList,Column(dt,i) << get formula);
              insertinto(formCols,i);
       );
       show(formList,formCols)
);
 
existingCols = dt << get column names(string);
For(i=1,i<=nitems(existingCols), i++,
 
       there = keepCols[existingCols[i]];
       If(there == 0,
              for(j=1,j<=nitems(formList),j++,
                     nowCol = existingCols[i];
                     If(Contains(char(formList[j]),nowCol) > 0,
                           Print(nowCol);
                           //show(formCols[j]);
                           Try(Column(dt,formCols[j]) << delete formula);
                           ,
                           Empty()
                     )
              );
              //dt << delete columns(eval(existingCols[i]))
       )
);
For(i=1,i<=nitems(existingCols), i++,
 
       there = keepCols[existingCols[i]];
       If(there == 0,
              dt << delete columns(eval(existingCols[i]))
       )
);

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\Big Class.jmp" );
dt << New Column( "Test 1", Numeric, Formula( :height + :weight ) );
colList = {:age, :height, :name};
colListStr = {"age", "height", "name"};
failed = {};
 
//dt << Delete Columns( :age );  //works
//dt << Delete Columns( colList[1] );  //fails
//dt << Delete Columns( Column( colListStr[1] ) ); //works
 
For( i = 1, i <= N Items( colList ), i++,
    //dt << Clear Column Selection;
    //colList[i] << Set Selected;
    str = "";
//str = Log Capture( dt << Delete Columns( colList[i] ) ); //fails
    //str = Log Capture( dt << Delete Columns() ); //works for selected columns
    str = Log Capture( dt << Delete Columns( Column( colListStr[i] ) ) ); //works
    Show( str );
    If( str != "",
        Insert Into( failed, colList[i] )
    );
);
Show( failed );