Mapping Columns in two different Tables by manual Selection using JSL

[#1]

I have two tables, let call them dt1 and dt2

Each table has one column each Column1 in dt1 and column2 in dt2.

I need to (either update or create a new table ) that has contents of Column1 mapped to to contents of Column2 by manual selection. Since the column entities are so different a join or update feature cannot be used. 

My aim is to create a user interface that allows the user to select row by row matching of content in the two columns. 

[#2]

Would the "Apply Mapping from Table" option in the Recode platform be useful for your situation? If so, it can be incorporated into a script.

[#3]

Hi Jed_campbell, 

Unfortunately no. This assume that a list exists that maps one column to another. I am practically trying to create such a list. 

So for this question purpose let assume that I have only two tables dt1 and dt2 and each of them has only one column, column1 and column2 respectively and I want to map some contents of column 1 to contents of column2. ( I use "some contents" because the number of rows in the two columns is different)

[#4 · Accepted solution]

Ah, something like this should work for you then, assuming I'm understanding correctly. You'd replace the first 2 tables with your own.

Names Default To Here( 1 );

dt1 = New Table( "Table 1",
	Add Rows( 4 ),
	New Column( "Choice1", Character, "Nominal", Set Values( {"A", "B", "C", "D"} ) )
);

dt2 = New Table( "Table 2",
	Add Rows( 5 ),
	New Column( "Choice2", Character, "Nominal", Set Values( {"L", "M", "N", "O", "P"} ) )
);

dtlookup = New Table( "lookuptable",
	Add Rows( 0 ),
	New Column( "dt1choice", Character, "Nominal" ),
	New Column( "dt2choice", Character, "Nominal" )
);
lst1 = Associative Array( dt1:Choice1 ) << get keys;
lst2 = Associative Array( dt2:Choice2 ) << get keys;



nw = New Window( "Chooser",
	H List Box(
		lb1 = List Box( lst1, max selected( 1 ) ),
		lb2 = List Box( lst2, max selected( 1 ) ), 
		
	),
	Button Box( "Make Match",
		dtlookup << add rows(
			{dt1choice = (lb1 << get selected)[1], dt2choice = (lb2 << get selected)[1]}
		)
	)
);

[#5]

Hi Jed_Campbell, This should in principle work but I get the following error when trying to run this code on already opened tables dt1 with Column Choice1 and dt2 with Column Choice2. I am using JMP17. 

Scoped data table access requires a data table column or variable in access or evaluation of 'dt1:Choice1' , dt1:Choice1/*###*/

[#6]

I fixed the issue. Thank you so much for your help. This is working now

[#7 · Accepted solution]

Here is very quickly thrown together UI which slightly follows JMP's table join/update platforms

View more...

/*""" Map two columns together

Author: jthi
Creation Date: 2025-06-05
Creation JMP Version: JMP Pro 18.2.0
	
"""*/


Names Default To Here(1);

dt1 = New Table("Untitled", New Column("Column 1", Character, "Nominal", Set Values({"A", "B", "C"})));
dt2 = New Table("Untitled", New Column("Column 2", Character, "Nominal", Set Values({"E", "F", "G", "H"})));


nw = New Window("Matcher",
	Lineup Box(N Col(3),
		V List Box(
			Panel Box("From - Table: " || (dt1 << get name || ", Column: " || (Column(dt1, 1) << get name)),
				window:lb_from = List Box(Column(dt1, 1) << get values)
			),
			Panel Box("To - Table: " || (dt2 << get name || ", Column: " || (Column(dt2, 1) << get name)),
				window:lb_to = List Box(Column(dt2, 1) << get values)
			)
		),
		Panel Box("Match",
			H List Box(align("top"),
				Button Box("Match", 
				<< Set Function(function({this},
					from_selections = window:lb_from << get selected;
					to_selections = window:lb_to << get selected;
					pairs = {};
					For Each({{from, to}}, Across(from_selections, to_selections, Count("Shortest")),
						Insert Into(pairs, Eval Insert("^from^=^to^"));
					);
					window:lb_match << Append(pairs);
				))),
				window:lb_match = List Box({}, << Set N Lines(20))
			)
		),
		Panel Box("Actions",
			Lineup Box(N Col(1),
				Button Box("OK", << Set Function(function({this},
					selections = window:lb_match << get items;
					from_values = {};
					to_values = {};
					For Each({selection}, selections,
						{from, to} = Words(selection, "=");
						Insert Into(from_values, from);
						Insert Into(to_values, to);
					);
					dt_result = New Table(Eval Insert("^(dt1 << get name)^ mapped to ^(dt2 << get name)^"),
						New Column(Column(dt1, 1) << get name, Character, Nominal, Values(from_values)),
						New Column(Column(dt2, 1) << get name, Character, Nominal, Values(to_values))
					);
					If(!(window:cb_keepdialog << get),
						this << close window;
					);
				))),
				Button Box("Cancel", << Set Function(function({this}, this << close window))),
				Spacer Box(Size(0, 10)),
				Button Box("Remove",
					window:lb_match << Remove Selected();
				),
				window:cb_keepdialog = Check Box({"Keep Dialog Open"}, << Set All(1))
			)
		)
	)
);

Write();

There are plenty of improvements which could be done such as column selection, cleaning UI, error handling, ...

[#8]

Hi Jarmo, Thank you so much. It is very helpful. In your code you compare first columns of each table. Can we modify it where specific column of one table at random location can be compared to a specific column ( irrespective of column number) in the second table? 

[#9]

It can easily be modified in such a way. Would the user pick column from the first table and then it is assumed same column exist in second table? Or should user be able to pick columns from both tables?

[#10]

Preferably, user should be able to pick columns from both the tables. 

Thanks a lot.