cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
New to using JMP? Hit the ground running with the Early User Edition of Discovery Summit. Register now, free of charge.
Register for our Discovery Summit 2024 conference, Oct. 21-24, where you’ll learn, connect, and be inspired.
%3CLINGO-SUB%20id%3D%22lingo-sub-50466%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%EB%85%BC%EB%A6%AC%EC%99%80%20%EC%9D%BC%EC%B9%98%ED%95%98%EB%8A%94%20%EB%91%90%20%EA%B0%9C%20%EC%9D%B4%EC%83%81%EC%9D%98%20%EC%97%B4%EC%9D%B4%EC%9E%88%EB%8A%94%20JMP%20%EC%A1%B0%EC%9D%B8%20%ED%85%8C%EC%9D%B4%EB%B8%94%20%22%EB%98%90%EB%8A%94%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-50466%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3E%EC%95%88%EB%85%95%20%EB%AA%A8%EB%91%90%2C%3C%2FP%3E%3CP%3E%EB%82%98%EB%8A%94%20%EC%95%84%EB%9E%98%EC%9D%98%20%EB%AC%B8%EC%A0%9C%EC%97%90%20%EB%8C%80%ED%95%9C%20%ED%95%B4%EA%B2%B0%EC%B1%85%EC%9D%B4%20%EC%9D%B4%EB%AF%B8%20%EC%A1%B4%EC%9E%AC%20%ED%95%98%EB%8A%94%EC%A7%80%EB%A5%BC%20%ED%8C%8C%ED%97%A4%EC%B3%90%EB%B3%B4%EA%B3%A0%EC%9E%88%EB%8B%A4.%3C%2FP%3E%3CP%3E%EB%A7%90%ED%95%B4%EB%B4%90%3C%2FP%3E%3CP%3E%3CSTRONG%3E3%20%EA%B0%9C%EC%9D%98%20%EC%97%B4%20%22X%22%2C%20%22Y%22%26amp%3B%20%22Z%22%EA%B0%80%EC%9E%88%EB%8A%94%20%ED%91%9C%20A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%ED%91%9C%20B%EB%8A%94%201%20%EC%97%B4%20%22R%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%EC%97%B4%20R%EC%97%90%EB%8A%94%20%EC%9D%B4%EB%AF%B8%20X%20%EB%B0%8F%20Y%20%EA%B0%92%EC%9D%B4%20%ED%8F%AC%ED%95%A8%EB%90%98%EC%96%B4%20%EC%9E%88%EC%8A%B5%EB%8B%88%EB%8B%A4.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%ED%85%8C%EC%9D%B4%EB%B8%94%20A%EC%99%80%20%ED%85%8C%EC%9D%B4%EB%B8%94%20B%EB%A5%BC%20%ED%95%A9%EC%B9%98%EB%A0%A4%EB%A9%B4%20---%26gt%3B%20%EC%9D%BC%EC%B9%98%20%EC%97%B4%20(X%20%3D%20R)%20%EB%98%90%EB%8A%94%20%EC%9D%BC%EC%B9%98%20%EC%97%B4%20(Y%20%3D%20R)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EJSL%20%EB%93%B1%EC%9C%BC%EB%A1%9C%20%ED%95%A0%20%EC%88%98%20%EC%9E%88%EC%8A%B5%EB%8B%88%EA%B9%8C%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%EA%B0%90%EC%82%AC%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%EB%9E%A8%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-50875%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%20%3A%20%EB%85%BC%EB%A6%AC%EC%99%80%20%EC%9D%BC%EC%B9%98%ED%95%98%EB%8A%94%20%EB%91%90%20%EA%B0%9C%20%EC%9D%B4%EC%83%81%EC%9D%98%20%EC%97%B4%EC%9D%B4%EC%9E%88%EB%8A%94%20JMP%20%EC%A1%B0%EC%9D%B8%20%ED%85%8C%EC%9D%B4%EB%B8%94%20%22OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-50875%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%EC%A7%90%20%EA%B3%A0%EB%A7%99%EB%8B%A4.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-50475%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%20%3A%20%EB%85%BC%EB%A6%AC%EC%99%80%20%EC%9D%BC%EC%B9%98%ED%95%98%EB%8A%94%20%EB%91%90%20%EA%B0%9C%20%EC%9D%B4%EC%83%81%EC%9D%98%20%EC%97%B4%EC%9D%B4%EC%9E%88%EB%8A%94%20JMP%20%EC%A1%B0%EC%9D%B8%20%ED%85%8C%EC%9D%B4%EB%B8%94%20%22OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-50475%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3E%EB%82%98%EB%8A%94%20%EB%8B%B9%EC%8B%A0%EC%9D%B4%202%20%EA%B0%9C%EC%9D%98%20%EA%B0%84%EB%8B%A8%ED%95%9C%20%EC%A1%B0%EC%9D%B8%EC%9C%BC%EB%A1%9C%20%EC%9B%90%ED%95%98%EB%8A%94%20%EA%B2%83%EC%9D%84%20%EC%84%B1%EC%B7%A8%20%ED%95%A0%20%EC%88%98%EC%9E%88%EB%8B%A4.%20...%20%EB%82%B4%20%EC%8A%A4%ED%81%AC%EB%A6%BD%ED%8A%B8%EB%A5%BC%20%EB%B3%B4%EC%95%84%EB%9D%BC.%20%ED%95%98%EB%82%98%EC%9D%98%20%EB%8D%B0%EC%9D%B4%ED%84%B0%20%ED%85%8C%EC%9D%B4%EB%B8%94%EC%97%90%EC%9E%88%EB%8A%94%20%EC%9D%B4%EB%A6%84%EC%9D%B4%20%EB%8B%A4%EB%A5%B8%20%EB%8D%B0%EC%9D%B4%ED%84%B0%20%ED%85%8C%EC%9D%B4%EB%B8%94%EC%97%90%EC%9E%88%EB%8A%94%20%EC%B2%AB%20%EB%B2%88%EC%A7%B8%20%EB%98%90%EB%8A%94%20%EB%A7%88%EC%A7%80%EB%A7%89%20%EC%9D%B4%EB%A6%84%EC%9D%B8%EC%A7%80%20%EB%AA%A8%EB%A5%B8%EB%8B%A4.%3C%2FP%3E%0A%3CPRE%3E%3CCODE%20class%3D%22%20language-jsl%22%3ENames%20Default%20to%20Here(1)%3B%0A%0A%2F%2F%20Create%20Sample%20Data%0Adt1%20%3D%20New%20Table(%20%22First%20and%20Last%20names%22%2C%0A%20Add%20Rows(%2010%20)%2C%0A%20New%20Column(%22Firstname%22%2CCharacter%2C%0A%20%20%22Nominal%22%2C%0A%20%20Set%20Values(%0A%20%20%20%7B%22KATIE%22%2C%20%22LOUISE%22%2C%20%22JANE%22%2C%20%22JACLYN%22%2C%20%22LILLIE%22%2C%20%22TIM%22%2C%20%22JAMES%22%2C%20%22ROBERT%22%2C%0A%20%20%20%22BARBARA%22%2C%20%22ALICE%22%7D%0A%20%20)%0A%20)%2C%0A%20New%20Column(%22lastname%22%2C%20Character%2C%0A%20%20%22Nominal%22%2C%0A%20%20Set%20Values(%0A%20%20%20%7B%22McMillan%22%2C%20%22Johnson%22%2C%20%22Jones%22%2C%20%22Smith%22%2C%20%22McDonald%22%2C%20%22Carson%22%2C%20%22Brady%22%2C%0A%20%20%20%22Williams%22%2C%20%22Wilt%22%2C%20%22Hamilton%22%7D%0A%20%20)%0A%20)%2C%0A%20New%20Column(%20%22sex%22%2C%20Character(%201%20)%2C%0A%20%20%22Nominal%22%2C%0A%20%20Set%20Values(%20%7B%22F%22%2C%20%22F%22%2C%20%22F%22%2C%20%22F%22%2C%20%22F%22%2C%20%22M%22%2C%20%22M%22%2C%20%22M%22%2C%20%22F%22%2C%20%22F%22%7D%20)%0A%20)%2C%0A%20New%20Column(%22height%22%2C%0A%20%20Numeric%2C%0A%20%20%22Continuous%22%2C%0A%20%20Format(%20%22Fixed%20Dec%22%2C%205%2C%200%20)%2C%0A%20%20Set%20Values(%20%5B59%2C%2061%2C%2055%2C%2066%2C%2052%2C%2060%2C%2061%2C%2051%2C%2060%2C%2061%5D%20)%0A%20)%2C%0A%20New%20Column(%22weight%22%2C%0A%20%20Numeric%2C%0A%20%20%22Continuous%22%2C%0A%20%20Format(%20%22Fixed%20Dec%22%2C%205%2C%200%20)%2C%0A%20%20Set%20Values(%20%5B95%2C%20123%2C%2074%2C%20145%2C%2064%2C%2084%2C%20128%2C%2079%2C%20112%2C%20107%5D%20)%0A%20)%2C%0A%20Set%20Label%20Columns(%20%3AFirstname%20)%0A)%3B%0A%0Adt2%20%3D%20New%20Table(%20%22ages%22%2C%0A%20New%20Column(%20%22Unknown%20names%22%2C%0A%20%20Character%2C%0A%20%20%22Nominal%22%2C%0A%20%20Set%20Values(%0A%20%20%20%7B%22KATIE%22%2C%20%22LOUISE%22%2C%20%22Jones%22%2C%20%22JACLYN%22%2C%20%22LILLIE%22%2C%20%22Carson%22%2C%20%22JAMES%22%2C%0A%20%20%20%22ROBERT%22%2C%20%22Wilt%22%2C%20%22ALICE%22%7D%0A%20%20%0A%20))%2C%0A%20New%20Column(%20%22age%22%2C%20Numeric%2C%0A%20%20%22Ordinal%22%2C%0A%20%20Format(%20%22Fixed%20Dec%22%2C%205%2C%200%20)%2C%0A%20%20Set%20Values(%20%5B12%2C%2012%2C%2012%2C%2012%2C%2012%2C%2012%2C%2012%2C%2012%2C%2013%2C%2013%5D%20)%0A%20)%2C%0A%20Set%20Label%20Columns(%20%3AUnknown%20Names%20)%0A)%3B%0A%0A%2F%2F%20Join%20Firstnames%20with%20the%20unknown%20names%0Adt3%20%3D%20dt1%20%26lt%3B%26lt%3B%20Join(%0A%20With(%20dt2%20)%2C%0A%20Merge%20Same%20Name%20Columns%2C%0A%20By%20Matching%20Columns(%20%3AFirstname%20%3D%20%3AUnknown%20names%20)%2C%0A%20Drop%20multiples(%200%2C%200%20)%2C%0A%20Include%20Nonmatches(%201%2C%200%20)%2C%0A%20Preserve%20main%20table%20order(%201%20)%0A)%3B%0A%0A%2F%2F%20Join%20the%20Lastnames%20with%20the%20unknown%20names%0Adt4%20%3D%20dt3%20%26lt%3B%26lt%3B%20Join(%0A%20With(%20dt2%20)%2C%0A%20Merge%20Same%20Name%20Columns%2C%0A%20By%20Matching%20Columns(%20%3ALastname%20%3D%20%3AUnknown%20names%20)%2C%0A%20Drop%20multiples(%200%2C%200%20)%2C%0A%20Include%20Nonmatches(%201%2C%200%20)%2C%0A%20Preserve%20main%20table%20order(%201%20)%0A)%3B%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Choose Language Hide Translation Bar
ram
ram
Level IV

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

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

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

View solution in original post

2 REPLIES 2
txnelson
Super User

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

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 )
);
Jim
ram
ram
Level IV

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

Thank you Jim.