topic Re: Update column with formula in Discussions

Update column with formula

UserID16644 — Sat, 10 Jun 2023 23:44:41 GMT

Hi all,

I have two data table (dt1 & dt2). I need to add dt2's column (Col1) to dt1 with a formula in it. But when I update the table, the values from Col1 are empty. Seems to be a problem in the formula. Please help

current data table (dt1); 				
dt1 << Update( With( dt2), Match Columns( :KeySample= :KeySample), Add Columns from Update table ( :Col1) );
dt1:Remarks << Formula ( If( :Col1== "Out of Range", "Out of Range", "" ));

Re: Update column with formula

txnelson — Thu, 24 Feb 2022 01:59:54 GMT

I am not finding an error with your code. I adopted it to a couple of sample data tables that I have attached, and it works without error.

names default to here(1);
dt1=data table ("bc1");
dt2=data table("bc2"); 				
dt1 << Update( With( dt2), Match Columns( :name= :name, :sex==:sex),
 Add Columns from Update table ( :Age) );
dt1:Remarks << Formula ( If( :age== "12", "Out of Range", "" ));

Check to make sure that my code works on your copy of JMP. If it does(which it should) then look to see how my sample data table's columns are different from your tables.

Re: Update column with formula

UserID16644 — Thu, 24 Feb 2022 02:57:24 GMT

Hi txnelson, from my example, the added column also has a formula which will become blank after update. I also tried delete formula before updating the table, but still, the column is blank

Re: Update column with formula

txnelson — Thu, 24 Feb 2022 04:25:34 GMT

Can you attach some samples of the data tables?

Re: Update column with formula

ZF — Thu, 24 Feb 2022 05:11:15 GMT

Try Copy column properties from dt1, then past column properties to dt2. When use update table, the formula got lost.

Names default to here(1);

// → Data Table( "Big Class" )
dt1=Open( "$SAMPLE_DATA/Big Class.jmp" );
// Launch platform: Bivariate
Biv= dt1<<Bivariate( Y( :weight ), X( :height ), Fit Line(1) );

Biv << (Curve["Linear Fit"] << Save Predicteds);

dt2 = New Table( "New ht",
	New Column( "height", numeric, continuous, set values([59,75]) ),
	New Column( "weight", numeric, continuous ),	
);

dt1 << Copy Column Properties ({:Predicted weight});
dt2 << Paste Column Properties ({: weight});

// Update data tables lose formula
dt2 << Update(
	With( dt1 ),
	Match Columns( :height = :height ),
	Add Columns from Update Table( :Predicted weight ),
	Replace Columns in Main Table( None )
);

Re: Update column with formula

owg — Thu, 30 May 2024 18:53:52 GMT

I had a similar problem using concatenate. The combined table had entries for the original table but not the table of new rows.

The solution was to check the box "Save and evaluate formulas".

This is much easier than adding the formula columns to the table of new rows before concatenating.

Rob.