Concatenate and Suppress Formulas

[#1]

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

[#2]

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;

[#3]

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

[#4]

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

[#5]

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

[#6]

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

[#7]

Are you sure you are trying to access correct datatable? You are always opening the same file as file_1 in the for loop.

You could add some debug prints inside for loop to help you finding out what is the problem, such as:

		file_1 = Open(SD1 || b[1]);
		show(file_1);
		show(file_1 << Get Column Names("String")); //depending how many columns you have might not want to do this
		show(Contains(file_1 << Get Column Names("String"), "Pore Diameter (um)"));
		file_1:"Pore Diameter (um)" << delete formula;

Also remember to close datatables you don't need anymore with Close(dtref, no save);

[#8]

Jarmo,

Many thanks for illuminating the issue. I didn't realize there would be an issue without an existing formula. With that fact in mind, I created a conditional statement for the first table. This obviously worked since "file_1" has the specific column formula deleted once. The attached code worked for the single column example.

Now that it is understand that a formula needs to be present, the issue is that all the columns do not have formulas. So if I retrieve all the column names, there will a subset that has no associated columns. Is there a way to test if a column has a formula so a proper list can be constructed to loop? Or do I just "brute force" the issue by setting up a list in JSL which I will have to update every time when I add a formula to a column?

Neil

For( i=1, i <= N Items(b), i++,
		
		If(i==1,
			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);	
			Close(file_nxt, no save);
		);
	);
	
	file_1 << Save(SD1 || "All Summary");

	Close("All Summary");

[#9 · Accepted solution]

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)); //the show part isn't 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 

[#10]

Jarno,

Great input. In fact I was starting to look at the "Is Empty" route last night. So to finalize the discussion to eliminate formulas and concatenate, I took your suggestions and updated my skillset by coding your suggestions. In addition, per my original post and code, I made updates that worked as well.

- First block is using the "Is Empty" statement to single out NO formula columns, and just eliminate formulas of the rest.

- Second block is using "Try" which I didn't fully understand at first when you suggested it even after I looked at the scripting index. I thought execution would stop like previous attempts, but obviously not.

- Third block uses  "Suppress formula eval" for the first table opened. All other tables are concatenated to this first table, which also gives a desired output where final concatenated table columns are not evaluated.

I can't thank you enough for al your input to help me improve my skills.

Neil

For( i=1, i <= N Items(b), i++,
		
		If(i==1,
			file_1 = Open(SD1 || b[1]);
			
			CN = file_1 << get column names(numeric);
			For(j=1, j <= N Items(CN), j++,
				result = Column(file_1, CN[j]) << Get Formula;
				y = Is Empty(result);
				If(y==0, Column(file_1, CN[j]) << delete formula);
			);
		);
		
		If(i > 1, 
			file_nxt = Open(SD1 || b[i], Private);
			file_1 << Concatenate(file_nxt, append to first table);	
			Close(file_nxt, no save);
		);
	);
	
	file_1 << Save(SD1 || "All Summary");
	Close("All Summary");

For( i=1, i <= N Items(b), i++,
		
		If(i==1,
			file_1 = Open(SD1 || b[1]);
			
			CN = file_1 << get column names(numeric);
			For(j=1, j <= N Items(CN), j++,
				Try(Column(file_1, CN[j]) << delete formula);
			);
		);
		
		If(i > 1, 
			file_nxt = Open(SD1 || b[i], Private);
			file_1 << Concatenate(file_nxt, append to first table);	
			Close(file_nxt, no save);
		);
	);
	
	file_1 << Save(SD1 || "All Summary");
	Close("All Summary");

For( i=1, i <= N Items(b), i++,
		
		If(i==1,
			file_1 = Open(SD1 || b[1]);
			file_1 << Suppress Formula Eval(1);
		);
		
		If(i > 1, 
			file_nxt = Open(SD1 || b[i], Private);
			file_1 << Concatenate(file_nxt, append to first table);	
			Close(file_nxt, no save);
		);
	);
	
	file_1 << Save(SD1 || "All Summary");
	Close("All Summary");