JMP Join Tables with 2 or more Column matching with Logical "OR

[#1]

Hi All,

I am looking to dig into if there is a solution already exist for below problem.

Say i have

Table A with 3 columns "X", "Y" & "Z"

Table B with 1 column "R"

Column R already contains some values of X and Y.

I want to merge Table A and Table B like --->  Match column (X=R) OR Match column (Y=R)

Can it be done with JSL or something?

Thanks

Ram

[#2 · Accepted solution]

I thing you can accomplish what you want with 2 simple joins....see my script where one does not know if a name in one data table is the first or last name in the other data table

Names Default to Here(1);

// Create Sample Data
dt1 = New Table( "First and Last names",
	Add Rows( 10 ),
	New Column("Firstname",Character,
		"Nominal",
		Set Values(
			{"KATIE", "LOUISE", "JANE", "JACLYN", "LILLIE", "TIM", "JAMES", "ROBERT",
			"BARBARA", "ALICE"}
		)
	),
	New Column("lastname", Character,
		"Nominal",
		Set Values(
			{"McMillan", "Johnson", "Jones", "Smith", "McDonald", "Carson", "Brady",
			"Williams", "Wilt", "Hamilton"}
		)
	),
	New Column( "sex", Character( 1 ),
		"Nominal",
		Set Values( {"F", "F", "F", "F", "F", "M", "M", "M", "F", "F"} )
	),
	New Column("height",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 5, 0 ),
		Set Values( [59, 61, 55, 66, 52, 60, 61, 51, 60, 61] )
	),
	New Column("weight",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 5, 0 ),
		Set Values( [95, 123, 74, 145, 64, 84, 128, 79, 112, 107] )
	),
	Set Label Columns( :Firstname )
);

dt2 = New Table( "ages",
	New Column( "Unknown names",
		Character,
		"Nominal",
		Set Values(
			{"KATIE", "LOUISE", "Jones", "JACLYN", "LILLIE", "Carson", "JAMES",
			"ROBERT", "Wilt", "ALICE"}
		
	)),
	New Column( "age", Numeric,
		"Ordinal",
		Format( "Fixed Dec", 5, 0 ),
		Set Values( [12, 12, 12, 12, 12, 12, 12, 12, 13, 13] )
	),
	Set Label Columns( :Unknown Names )
);

// Join Firstnames with the unknown names
dt3 = dt1 << Join(
	With( dt2 ),
	Merge Same Name Columns,
	By Matching Columns( :Firstname = :Unknown names ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 0 ),
	Preserve main table order( 1 )
);

// Join the Lastnames with the unknown names
dt4 = dt3 << Join(
	With( dt2 ),
	Merge Same Name Columns,
	By Matching Columns( :Lastname = :Unknown names ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 0 ),
	Preserve main table order( 1 )
);

[#3]

Thank you Jim.