445424_en-US

liliconcom

Concatenate and Suppress Formulas

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

  

Data TableScriptingRe: Concatenate and Suppress Formulas

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;

Re: Concatenate and Suppress Formulas

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

Re: Concatenate and Suppress Formulas

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

Re: Concatenate and Suppress Formulas

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

Re: Concatenate and Suppress Formulas

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

Re: Concatenate and Suppress Formulas

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