cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
TWE
TWE
Level III

Table Update with get rows

Hi,

 

I have two tables with > 50k rows and 5 -10 columns and I want to update the first table (dt1) with values from the second table (dt2). I dont want to use loops because it could take a lot of time with rows > 50k. The JSL "update" function works well but I need also the matched rows in dt2 for further use.

To show what I mean I used the "update" example from "script index" and add the name MIKE with height, weight, rank and code. I need a list with row number of matched rows. For that example it would be {1, 2, 3} because MIKE doesnt exists in dt1.

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( {"KATIE", "ALFRED", "HENRY", "MIKE"} ) ),
	New Column( "height", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "weight", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "RANK", Continuous, Set Values( [3, 1, 2, 9] ) ),
	New Column( "CODE", Continuous, Set Values( [0, 1, 1, 8] ) )
);
dt1 << Update(
	With( Data Table( "Little Class" ) ),
	Match Columns( :name = :name ),
	Add columns from Update table( {:RANK} ),
	Replace columns in Main Table( {:height} )
);

Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions
cwillden
Super User (Alumni)

Re: Table Update with get rows

Here's one way that might work for you assuming name acts as a unique identifier:

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( {"KATIE", "ALFRED", "HENRY", "MIKE"} ) ),
	New Column( "height", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "weight", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "RANK", Continuous, Set Values( [3, 1, 2, 9] ) ),
	New Column( "CODE", Continuous, Set Values( [0, 1, 1, 8] ) )
);
dt1 << Update(
	With( Data Table( "Little Class" ) ),
	Match Columns( :name = :name ),
	Add columns from Update table( {:RANK} ),
	Replace columns in Main Table( {:height} )
);

//Get names as associative arrays
names1 = Associative Array(dt1:name << Get values);
names2 = Associative Array(dt2:name << Get Values);

//Get the intersection (replaces names1)
names1 << Intersect(names2);

//Get intersection names as List
intersection = names1 << Get Keys;

//Find rows
update_rows = dt2 << Get Rows Where(contains(intersection, :name)>0);
-- Cameron Willden

View solution in original post

txnelson
Super User

Re: Table Update with get rows

I suggest that you use a Join instead of the Update.  If setup correctly, it will work like the Update, but it will add new rows.  It also will provide you with a column that will indicate which rows were added.

The drawback is that it creates a new data table, but with a couple of statements, that can be dealt with, and it will look and feel just like an update had been done.  Look at the code below:

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( {"KATIE", "ALFRED", "HENRY", "MIKE"} ) ),
	New Column( "height", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "weight", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "RANK", Continuous, Set Values( [3, 1, 2, 9] ) ),
	New Column( "CODE", Continuous, Set Values( [0, 1, 1, 8] ) )
);
dt3 = dt1 << Join(
	With( dt2),
	Merge Same Name Columns,
	By Matching Columns( :name = :name ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 1 )
);

name = dt1 << get name;

close(dt1, nosave );

dt3 << set name(name);
dt1 = dt3;
Jim

View solution in original post

4 REPLIES 4
cwillden
Super User (Alumni)

Re: Table Update with get rows

Here's one way that might work for you assuming name acts as a unique identifier:

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( {"KATIE", "ALFRED", "HENRY", "MIKE"} ) ),
	New Column( "height", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "weight", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "RANK", Continuous, Set Values( [3, 1, 2, 9] ) ),
	New Column( "CODE", Continuous, Set Values( [0, 1, 1, 8] ) )
);
dt1 << Update(
	With( Data Table( "Little Class" ) ),
	Match Columns( :name = :name ),
	Add columns from Update table( {:RANK} ),
	Replace columns in Main Table( {:height} )
);

//Get names as associative arrays
names1 = Associative Array(dt1:name << Get values);
names2 = Associative Array(dt2:name << Get Values);

//Get the intersection (replaces names1)
names1 << Intersect(names2);

//Get intersection names as List
intersection = names1 << Get Keys;

//Find rows
update_rows = dt2 << Get Rows Where(contains(intersection, :name)>0);
-- Cameron Willden
TWE
TWE
Level III

Re: Table Update with get rows

Thanks, this works well, also for a high amount of rows.
txnelson
Super User

Re: Table Update with get rows

I suggest that you use a Join instead of the Update.  If setup correctly, it will work like the Update, but it will add new rows.  It also will provide you with a column that will indicate which rows were added.

The drawback is that it creates a new data table, but with a couple of statements, that can be dealt with, and it will look and feel just like an update had been done.  Look at the code below:

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( {"KATIE", "ALFRED", "HENRY", "MIKE"} ) ),
	New Column( "height", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "weight", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "RANK", Continuous, Set Values( [3, 1, 2, 9] ) ),
	New Column( "CODE", Continuous, Set Values( [0, 1, 1, 8] ) )
);
dt3 = dt1 << Join(
	With( dt2),
	Merge Same Name Columns,
	By Matching Columns( :name = :name ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 1 )
);

name = dt1 << get name;

close(dt1, nosave );

dt3 << set name(name);
dt1 = dt3;
Jim
TWE
TWE
Level III

Re: Table Update with get rows

Thanks, I tried this approach but for more than 50k rows it will take too long. For smaller tables this works well.