878440_en-US

liliconcom

Mapping Columns in two different Tables by manual Selection using JSL

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. 

 

Automation and ScriptingRe: Mapping Columns in two different Tables by manual Selection using JSL

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

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

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

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

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

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, ...

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

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

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

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

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

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)

 

View more...