- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Update column with formula
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", "" ));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Update column with formula
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Update column with formula
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Update column with formula
Can you attach some samples of the data tables?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Update column with formula
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 )
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Update column with formula
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.