topic Re: Using Update() to add new rows into main table in Discussions

Using Update() to add new rows into main table

FN — Fri, 09 Jun 2023 19:48:43 GMT

I am trying to update a table that sometimes contains new rows (based on an index).

It seems that I need to use join, as the following does not seem to work (here, the index is name column).

Names Default To Here( 1 );
dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = New Table( "Little Class",
	Add Rows( 3 ),
	New Column( "name",
		Character,
		Nominal,
		Set Values( {"ALICE", "NEW_NAME", "OTHER_NEW_NAME"} )
	),
	New Column( "height",
		Continuous,
		Set Values( [999, 999, 999] )
	),
	New Column( "weight",
		Continuous,
		Set Values( [999, 999, 999] )
	),
	New Column( "RANK",
		Continuous,
		Set Values( [3, 1, 2] )
	),
	New Column( "CODE",
		Continuous,
		Set Values( [0, 1, 1] )
	)
);
dt1 << Update(
	With( Data Table( "Little Class" ) ),
	Match Columns( :name = :name ),
	Add columns from Update table( {:RANK} )
);

Re: Using Update() to add new rows into main table

txnelson — Sat, 05 Jun 2021 12:16:40 GMT

Your stated assumption is correct, you will have to use the Join platform to do what you want.

Re: Using Update() to add new rows into main table

jthi — Sat, 05 Jun 2021 12:24:30 GMT

Found similar post from community here and it was suggested to use Join instead of update when new rows are added:

Updating JMP Data Tables

Is there a reason why you couldn't use join instead of update (except that you will have to deal with extra tables)?

From JMP Help: Update Data Tables :

If you have two data tables and would like to update your original table with data from a new table, select Tables > Update. The Update command is a special case of Join in place. It is a Join with the Update option checked, and it does not result in a new table.

Re: Using Update() to add new rows into main table

FN — Sat, 05 Jun 2021 15:48:53 GMT

"Except that you will have to deal with extra tables"

Exactly. A new table is generated despite I am doing the join with the update option selected.

dt<< Join(
    With( dt_new ),
    Update, // this option is selected
    By Matching Columns( :index= :index),
    Drop multiples( 0, 0 ),
    Include Nonmatches( 1, 1 ),
    Preserve main table order( 1 )
);

// Opens new table apart of dt and dt_new

Re: Using Update() to add new rows into main table

jthi — Sat, 05 Jun 2021 15:54:01 GMT

I tested the same option and it behaved same way, it did create new table.

I can share the pain with dealing of open tables in JMP, but while scripting it is fairly easy (will add some extra code thou), open/create them as invisible/private and close them immediately without saving after you know you won't be needing them anymore.

Re: Using Update() to add new rows into main table

FN — Sat, 05 Jun 2021 16:43:56 GMT

I see. What would you recommend to keep this last step simple?

I have 'dt' (original) and the 'dt_joined' ('dt' + 'dt_new').

Re: Using Update() to add new rows into main table

jthi — Sat, 05 Jun 2021 16:57:25 GMT

Most of the time I would do it with Invisible tables, because that gives possibility to open them easily from JMPs window list (if they haven't been closed).

Invisible tables are in grey color (transparent) in the Window List and you make them visible by double clicking on them.

I also suggest adding names to tables, as it makes it easier to start debugging when you have lots of tables open and for some reason the code didn't execute properly. Without renaming they will be just became a mess like: Subset of something 1, Subset of something 2, Untitled 1 and so on.

Below is an example which will open dt as invisible and create dt_new as invisible table. Then join them to new table called JOINED_TABLE which will be shown to the user and finally close dt and dt_new without saving them.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp", invisible);
dt_new = New Table( "Little Class",
	Add Rows( 3 ),
	New Column( "name",
		Character,
		Nominal,
		Set Values( {"ALICE", "NEW_NAME", "OTHER_NEW_NAME"} )
	),
	New Column( "height",
		Continuous,
		Set Values( [999, 999, 999] )
	),
	New Column( "weight",
		Continuous,
		Set Values( [999, 999, 999] )
	),
	New Column( "RANK",
		Continuous,
		Set Values( [3, 1, 2] )
	),
	New Column( "CODE",
		Continuous,
		Set Values( [0, 1, 1] )
	),
	invisible
);

dt_joined = dt << Join(
	With(dt_new),
	Merge Same Name Columns,
	By Matching Columns(:name = :name),
	Drop multiples(0, 0),
	Include Nonmatches(1, 1),
	Preserve main table order(1),
	Output table("JOINED_TABLE")
);
Close(dt, no save);
Close(dt_new, no save);

Re: Using Update() to add new rows into main table

hogi — Tue, 27 Jun 2023 07:17:49 GMT

I wrote a wish to add an option to update the main table:

feel free to support it :)

Re: Using Update() to add new rows into main table

hogi — Wed, 28 Jun 2023 20:08:46 GMT

The new Join Platform could look like this:
https://community.jmp.com/t5/JMP-Wish-List/Tables-join-option-to-update-main-table/idc-p/653857/highlight/true#M4537