topic Re: How to delete reference of formula column in JSL in Discussions

How to delete reference of formula column in JSL

WebDesignesCrow — Fri, 30 Jun 2023 03:31:37 GMT

Hello expert,

I want to delete reference of formula column in JSL.

Manually, after I selected the columns to be deleted, I can click "Remove Formula" after JMP Alert prompted.

But, when I copied the workflow script & put it in my script, the reference of formula column still not deleted.

Any suggestion of how to move around? I'm using JMP 17

Thank you in advance

Re: How to delete reference of formula column in JSL

Jed_Campbell — Fri, 30 Jun 2023 15:12:43 GMT

I'm assuming that you are using the workflow builder. Have you tried recording yourself deleting the formula in the Workflow Builder? This should remove any dependencies. You could also do it via JSL using the example below.

Re: How to delete reference of formula column in JSL

WebDesignesCrow — Mon, 03 Jul 2023 01:21:41 GMT

Yes. I used workflow builder.

1)However, the workflow builder did not record "Delete Formula". Instead, it just recorded "Delete Columns" although I've selected "Remove formula" when the JMP Alert prompted.

2) I tried "Delete Formula" as well in my script but it didn't work.

Below is my script;

//Add new column formula to change Wirebond, 2nd Wirebond, Diebond and 2nd Diebond to correct date format
Names Default To Here( 1 );
dt4 = Data Table( "DB.WB(Datasheet)" );
dt4 << New Column("Wirebond.End Date", Numeric, Formula(
			(As Date( In Days( :Wirebond End Date) + Informat ("1/1/1900", "<MM></><D></><YYYY><hh><:><mm><:><ss><ampm>")) - In Days( 2 ))));
dt4 << New Column("Diebond.End Date", Numeric, Formula(
			(As Date( In Days( :Diebond End Date) + Informat ("1/1/1900", "<MM></><D></><YYYY><hh><:><mm><:><ss><ampm>")) - In Days( 2 ))));
dt4 << New Column("2nd Wirebond.End Date", Numeric, Formula(
			(As Date( In Days( :"2nd Wirebond End Date"n) + Informat ("1/1/1900", "<MM></><D></><YYYY><hh><:><mm><:><ss><ampm>")) - In Days( 2 ))));
dt4 << New Column("2nd Diebond.End Date", Numeric, Formula(
			(As Date( In Days( :"2nd Diebond End Date"n) + Informat ("1/1/1900", "<MM></><D></><YYYY><hh><:><mm><:><ss><ampm>")) - In Days( 2 ))));
//Change column info: Wirebond.End Date
Data Table( "DB.WB(Datasheet)" ):Wirebond.End Date <<
Input Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 0 )
 << Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 20, 0 );
//Change column info: Diebond.End Date
Data Table( "DB.WB(Datasheet)" ):Diebond.End Date <<
Input Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 0 )
 << Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 20, 0 );
//Change column info: 2nd Wirebond.End Date
Data Table( "DB.WB(Datasheet)" ):"2nd Wirebond.End Date"n <<
Input Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 0 )
 << Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 20, 0 );
//Change column info: 2nd Diebond.End Date
Data Table( "DB.WB(Datasheet)" ):"2nd Diebond.End Date"n <<
Input Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 0 )
 << Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 20, 0 );
//Remove data with End lot time < Today - 30 days (not required for analysis)
Current Data Table() <<  Select where( :End Lot Time < (today () - in days(30))  ) << Delete Rows;
//set Label for Machine No, Lot No, Base Part (Device), Recipe, Diebond & Wirebond MC
dt4 << set label columns( :Machine No, :Lot No, :Device, :Recipe, :Diebond MC ID, :Diebond End Date, :Wirebond MC ID, :Wirebond End Date, :Package, :Package Platform );
//Delete columns
Current Data Table() << Data Table( "DB.WB(Datasheet)" )
<< Delete Formula (:Diebond End Date, :"2nd Diebond End Date"n, :Wirebond End Date,:"2nd Wirebond End Date"n);

The log is as below

Re: How to delete reference of formula column in JSL

ih — Mon, 03 Jul 2023 18:17:29 GMT

You might be looking for Delete Formula.

From the scripting index:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Bank Loan.jmp" );
:Time << Delete Formula;

Re: How to delete reference of formula column in JSL

WebDesignesCrow — Tue, 04 Jul 2023 07:05:16 GMT

The script for "Delete Formula" didn't work.

Strange. It states the column has no formula when running script.

But, when I select the column manually to delete, it states "cannot delete column while it is referenced by the formula of other column"

Re: How to delete reference of formula column in JSL

Craige_Hales — Tue, 04 Jul 2023 11:23:14 GMT

"Diebond.End Date"

is the column with the formula that refers to

In Days( :Diebond End Date)

Try deleting the formula from the column with the period in the name.

@Jed_Campbell - that very first dialog, "cannot delete the selected columns while..." should add something like

Diebond End Date can't be deleted because it is in use by the formula for Diebond.End Date, ... etc

Re: How to delete reference of formula column in JSL

WebDesignesCrow — Wed, 05 Jul 2023 08:30:38 GMT

Thanks @Craige_Hales .

It works. I'll put a wishlist for the workflow on this matter.

//Delete columns
dt4 << Data Table( "DB.WB(Datasheet)" );	
Wait(0);
dt4:Diebond.End Date << Delete Formula;
dt4:"2nd Diebond.End Date"n << Delete Formula;
dt4:Wirebond.End Date << Delete Formula;
dt4:"2nd Wirebond.End Date"n << Delete Formula;
Wait(0);
dt4 << Delete Columns (:Diebond End Date, :"2nd Diebond End Date"n, :Wirebond End Date, :"2nd Wirebond End Date"n);