Table Update with get rows

[#1]

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 solution]

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

[#3]

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

[#4 · Accepted solution]

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;

[#5]

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