topic Re: Mapping Columns in two different Tables by manual Selection using JSL in Discussions

Mapping Columns in two different Tables by manual Selection using JSL

NewToThis — Thu, 05 Jun 2025 17:09:01 GMT

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.

Re: Mapping Columns in two different Tables by manual Selection using JSL

Jed_Campbell — Thu, 05 Jun 2025 17:40:24 GMT

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.

Re: Mapping Columns in two different Tables by manual Selection using JSL

NewToThis — Thu, 05 Jun 2025 18:17:27 GMT

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)

Re: Mapping Columns in two different Tables by manual Selection using JSL

Jed_Campbell — Thu, 05 Jun 2025 18:45:42 GMT

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

Re: Mapping Columns in two different Tables by manual Selection using JSL

NewToThis — Thu, 05 Jun 2025 19:24:16 GMT

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/*###*/

Re: Mapping Columns in two different Tables by manual Selection using JSL

NewToThis — Thu, 05 Jun 2025 19:28:12 GMT

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

Re: Mapping Columns in two different Tables by manual Selection using JSL

jthi — Thu, 05 Jun 2025 20:05:04 GMT

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

/*""" 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, ...

Re: Mapping Columns in two different Tables by manual Selection using JSL

NewToThis — Wed, 11 Jun 2025 04:38:42 GMT

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?

Re: Mapping Columns in two different Tables by manual Selection using JSL

jthi — Wed, 11 Jun 2025 04:43:34 GMT

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?

Re: Mapping Columns in two different Tables by manual Selection using JSL

NewToThis — Wed, 11 Jun 2025 20:04:54 GMT

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

Thanks a lot.

Re: Mapping Columns in two different Tables by manual Selection using JSL

jthi — Thu, 12 Jun 2025 06:04:43 GMT

I assume it would just be single column from both tables? I should have time today to add needed updates to the script.

Re: Mapping Columns in two different Tables by manual Selection using JSL

jthi — Thu, 12 Jun 2025 14:29:15 GMT

Creating this will take longer than I expected as user has to be also able to pick the second table which makes it very messy due to how all "nice" display boxes behave when tables change (looking at filter col selector and col list box...). And I have less time in the evening than I hoped I would have.

I also started creating more complicated UI but I realized that there really isn't a point as JMP's Recode platform should do all this. I suggest you create a wish list item about this (I can also create one, I do have few extra suggestions also)