915139_en-US

liliconcom

Data tables

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

 

Automation and ScriptingData AccessRe: Data tables

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

Re: Data tables

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

Re: Data tables

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=""> 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=""></SET></SET>