Data tables

Question

Hi,

I want to compare more than 2 tables in JMP for a common column present in all the tables. Now i want to return a 1 if the values in that specific column exists in each table otherwise 0. This im able to do for some predefined tables and by using a JSL script which adds an extra column in each table and return 0 or 1. 
But i want to make this script generic so that i dont need to predefine the name of tables. It should be like, whenever i run this script, it should ask me which tables i want to compare.

After that, i also want this script to work in such a way it can also give me the option to select which column i want to compare

Currently, Im using this script

// Get references to the three tables
dt1 = Data Table("Untitled 9");
dt2 = Data Table("Untitled 10");
dt3 = Data Table("Untitled 11");

// Extract ID values from each table
ids1 = dt1:ID << Get Values;
ids2 = dt2:ID << Get Values;
ids3 = dt3:ID << Get Values;

// Find common IDs manually
commonIDs = {};
For(i = 1, i <= N Items(ids1), i++,
	If(Contains(ids2, ids1[i]) & Contains(ids3, ids1[i]),
		Insert Into(commonIDs, ids1[i])
	)
);

// Function to add indicator column
AddIndicatorColumn = Function({dt},
	dt << New Column("PresentInAll",
		Numeric,
		Formula(If(Contains(commonIDs, :ID), 1, 0))
	)
);

// Apply to all tables
AddIndicatorColumn(dt1);
AddIndicatorColumn(dt2);
AddIndicatorColumn(dt3);

Edit (jthi): added jsl formatting

Selecting three tables Untitled 9, Untitled 10 and Untitled 11 and the column im comparing is "ID"

Please help on this,

Thanks

txnelson · Answer

Here is a fleshed out piece of JSL that takes Jarmo's JSL and expands on it to give you the results you show in your initial discussion.

It uses Associative arrays to find the intersection of both the column names that appear in all 3 tables, and then also in the values that appear in the selected column.

It also uses the Update Platform to move the results back into the original 3 tables.  The methodology should be pretty efficient even on very large data tables.

Names Default To Here( 1 );
dts = Get Data Table List();

If( N Items( dts ) < 2,
	Throw( "Not enough tables open" )
);

tables = dts << get name;

// Create the table and column selection window
nw = New Window( "",
	H List Box(
		// Define the Table selection box
		Panel Box( "Select tables",
			lb_tables = List Box(
				tables,
				maxselected( 3 ),
				<<Set Function(
					Function( {this},
						// Get the tables selected by the user
						sel = this << get selected;
						// If 3 tables have not been selected just return
						// otherwise, find all of the columns that are common
						// in all 3 tables.
						// Note: Only character columns are evaluated
						//       Column names must be named exactly the same way
						If( N Items( sel ) < 3,
							pickPBox << visibility( "Collapse" );
							Return( 0 );
						,
							// Assign shortcut handles to the 3 data tables 
							dtOne = Data Table( (lb_tables << get selected)[1] );
							dtTwo = Data Table( (lb_tables << get selected)[2] );
							dtThree = Data Table( (lb_tables << get selected)[3] );

// Extract the character column names from each data table and
							// move them into an Associative Array
							accOne = Associative Array( dtOne << get column names(character) );
							accTwo = Associative Array( dtTwo << get column names(character)  );
							accThree = Associative Array( dtThree << get column names(character)  );

// Use the Intersect() funtion to find common column names
							accOne << Intersect( accTwo );
							accOne << Intersect( accThree );
							cols = accOne << get keys;

// If at least one column name is found, display the columns
							// for selection
							If( Length( cols ) > 0,
								lb_columns << Set Items( cols );
								pickPBox << visibility( "Visible" );
							);
						);
					)
				)
			),

),
		// Define the column selection box
		pickPBox = Panel Box( "Pick Column",
			lb_columns = List Box(
				{},
				Max Selected( 1 ),
				<<Set Function(
					Function( {this},
						sel = this << get selected;
						// If a column has been selected, enable the OK button
						If( N Items( sel ) < 1,
							bbOK << enable( 0 );
							Return( 0 );
						,
							bbOK << enable( 1 )
						);
					)
				)
			)
		),
		// Define the Actions box
		Panel Box( "Actions",
			Lineup Box( N Col( 1 ), 
				xsel = sel[1];
				// Define the OK button and what to do when clicked
				bbOK = Button Box( "OK",
					// Copy the values from the selected column from each selected data table
					// into an Associative Array....Note: code is using the previously used arrays,
					// but recreating them with the new data
					accOne = Associative Array( Column( dtOne, xsel ) << get values );
					accTwo = Associative Array( Column( dtTwo, xsel ) << get values );
					accThree = Associative Array( Column( dtThree, xsel ) << get values );

// Use the Intersect() function to find all values found 
					// in the selected column in all 3 data tables
					accOne << Intersect( accTwo );
					accOne << Intersect( accThree );
					theArray = accOne << get keys;

// The method that is used to add the new column with Present in All column
					// uses the Update Platform where 2 tables are joined with matching rows
					// being added to the table being updated.  To do that, a temporary table
					// is created containing the common data values found in the previous step.

// Add a Transitional table
					dtMatch = New Table( "Match",
						add rows( Length( theArray ) ),
						New Column( xsel, character ),
						New Column( "PresentinAll", set each value( 1 ) )
					);
					// Add the common data values to the matching column
					Column( dtMatch, xsel ) << set values( theArray );

// For each of the 3 target tables, update them with the 
					// data from the Match data table
					dtOne << Update(
						With( dtMatch ),
						Match Columns( Column( dtOne, xsel ) = Column( dtMatch, xsel ) )
					);
					// The Updating added a 1 for all rows that have common column values,
					// and the statement below, add a 0 to all of the other rows in the column
					Try(
						dtOne:PresentinAll[dtOne << get rows where( Is Missing( dtOne:PresentinAll ) == 1 )]
						 = 0
					);
					dtTwo << Update(
						With( dtMatch ),
						Match Columns( Column( dtTwo, xsel ) = Column( dtMatch, xsel ) )
					);
					Try(
						dtTwo:PresentinAll[dtTwo << get rows where( Is Missing( dtTwo:PresentinAll ) == 1 )]
						 = 0
					);
					dtThree << Update(
						With( dtMatch ),
						Match Columns( Column( dtThree, xsel ) = Column( dtMatch, xsel ) )
					);
					Try(
						dtThree:PresentinAll[dtThree << get rows where(
							Is Missing( dtThree:PresentinAll ) == 1
						)] = 0
					);
					nw << close window;
					// Delete the temporary transistional data table.
					Close( dtMatch, nosave );
				),
				Button Box( "Cancel", nw << close window )
			)
		)
	)
);
// Set the initial values for the Column selection box and the OK button
pickPBox << visibility( "Collapse" );
bbOK << enable( 0 );

jthi · Answer

Anyway, you can build the UI using Get Data Table List + List Boxes. Below is quick version which can help with getting started

Names Default To Here(1);
dts = Get Data Table List();

If(N Items(dts) < 2,
	Throw("Not enough tables open");
);

run_expr = Expr(
	Show(lb_tables << get selected, lb_columns << get selected);
);

tables = dts << get name;

nw = New Window("",
	H List Box(
		Panel Box("Select tables", 
			lb_tables = List Box(tables, 
				<< Set Function(Function({this},
					sel = this << get selected;
					If(N Items(sel) < 1,
						return(0);
					);
					cols = Datatable(sel[1]) << Get Column Names("String");
					lb_columns << Set Items(cols);
				))
			),
		),
		Panel Box("Pick Column",
			lb_columns = List Box({}, << Set Max Selected(1))
		),
		Panel Box("Actions",
			Lineup Box(N Col(1),
				Button Box("OK",
					run_expr
				),
				Button Box("Cancel",
					nw << close window;
				)
			)
		)
	)
);

Write();

jthi · Answer

How many rows your tables have and many unique values? This can affect how this should be done to avoid performance issues.