cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
FN
FN
Level VI

Using Update() to add new rows into main table

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} )
);
		
1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

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

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.

jthi_1-1622911801553.png

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

View solution in original post

8 REPLIES 8
txnelson
Super User

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

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

Jim
jthi
Super User

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

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.

-Jarmo
FN
FN
Level VI

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

"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
jthi
Super User

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

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.

-Jarmo
FN
FN
Level VI

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

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

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

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

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.

jthi_1-1622911801553.png

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);
-Jarmo
hogi
Level XII

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

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

Tables join: option to update main table 

 

feel free to support it

hogi
Level XII

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