nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Concatenate and Suppress Formulas

NRW

Folks,

I have several tables (samples) that have many active column formulas. My script at one point, concatenates all the tables. However, I want the formulas to suppress since I want to keep the individual table data (sample) intact so as to compare / overlay all the data that was concatenated. Unfortunately, I can't seem to properly turn off the formulas when concatenated, and the newly concatenated columns are being evaluated which loses the individual sample data. I thought I had this beat with the code attached, but when I recopied the initial table to another folder, the formulas "reactivated" causing some frustration. If there is a good fix, I'm open to any input since the Suppress Formula Evaluation is not working in the current implementation.

Neil

Con = Expr(
	n = N Table();
	
		dt = {};

		// Sort list ascending (sample replicates) 

		For( i = 1, i <= n, i++,
			table = data table(i);
			insert into(dt, table);
			sort list into(dt)
		);
	
		// Concatenate each file (sample replicates) 
		
		dt_all = Data Table( dt[1] );
		
		For(i = 1, i <= n, i++,
			
			If(i > 1,
				Suppress Formula Eval(suppress=1);
				dt_all << Concatenate(Invisible, dt[i], Append to first table); 
			)	
			
		);
		
		Current Data Table(dt_all);
					
);

Accepted answers

jthi

If you don't necessarily need the formulas, no need to even create them as you can use << Set Each Value instead of formula (or remove formulas immediately after creation). But if you do need them you could check if column has a formula with << get formula and if the result is Empty() then formula doesn't exist (use IsEmpty() to check for missing formula) or you could "brute force" it with Try-catch.

Get Formula:

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");
col = New Column("Ratio");
result = col << Get Formula;
Show(result, IsEmpty(result));
col << Set Formula(:Height / :Weight);
col << Eval Formula;
result = col << Get Formula;
Show(result, IsEmpty(result));

Try-catch:

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");
col = New Column("Ratio");
Try(col << Delete formula, show(exception_msg));&nbsp;//the&nbsp;show&nbsp;part&nbsp;isn't&nbsp;necessary

Then if you want to loop just over columns which have formulas, you could for loop over the list and build new one (if you have JMP16 use Filter Each).

There are also some posts in community on how to remove all formulas in table, here is one for example: Find and Delete all formulas present in a table

All comments

txnelson

What you need to do, is to delete the formulas before concatenation. That will convert the values to static values and then you will not have an issue.

:yourColumn << delete formula;

NRW

Jim,

Thanks for the response. Since there are about 10 columns with formulas per table, is there "blanket" approach to deleting all at once?

Neil

txnelson

You can put the column names in a list, and then loop across the list, deleting each one.

jthi

JMP is also able to do this interactively with Standardize Attributes:

Select all columns, right click on one of the columns and select Standardize Attributes:

Add Formula to Delete Properties and press OK (or apply):

If you have JMP16 this will be printed to Enhanced Log:

// Open Data Table: Wheat.jmp
// → Data Table("Wheat")
Open("$SAMPLE_DATA/Wheat.jmp");


// Standardize column attributes
Local({old dt = Current Data Table()},
	Current Data Table(Data Table("Wheat"));
	For Each({col, index},
		{:Variety, :Yield, :Moisture, :Pred Formula Yield, :Cond Pred Formula Yield},
		col << Delete Formula
	);
	Current Data Table(old dt);
);

And this will give good idea how to handle this (same as txnelson said, loop over the list and delete formulas as you go). You can also get all columns in table with << Get Column Names. You won't be able to remove formulas from columns which don't have them (and the script provided by enhanced log wont work directly like that), but you can circumvent that by wrapping the col << Delete formula with Try(). After cleaning up the enhanced log script you might end up with something like this:

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Wheat.jmp");
For Each({col}, dt << get column names(String),
	Try(Column(dt, col) << Delete Formula)
);

NRW

Sorry for the late reply. I appreciate all the feed back.

Without attacking all the columns yet, I just tried eliminating one formula in a particular column. When I apply the "delete formula" line outside the loop, I get the desired result, that is no formula exists. However, if the same line goes within the loop, after the file is opened, I get the following error:

"Column Pore Diameter (um) has no formula in access or evaluation of 'delete formula' , delete formula/*###*/"

Obviously I don't have the mechanics understanding yet to see why there is an issue. Would you be able provide some insight?

Many thanks.

For( i=1, i <= N Items(b), i++,
		
		file_1 = Open(SD1 || b[1]);
		
		
		file_1:"Pore Diameter (um)" << delete formula;
		
		If(i > 1, 
			
			file_nxt = Open(SD1 || b[i], Private);
			file_1 << Concatenate(file_nxt, Append to first table);	
		);
		
		
	);
	
	//file_1:"Pore Diameter (um)" << delete formula;
	file_1 << Save(SD1 || "All Summary");
	Close("All Summary");