cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
%3CLINGO-SUB%20id%3D%22lingo-sub-50466%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3EJMP%20Join%20Tabelle%20con%202%20o%20pi%C3%B9%20colonne%20corrispondenti%20a%20Logical%20%22OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-50466%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3ECiao%20a%20tutti%2C%3C%2FP%3E%3CP%3ESto%20cercando%20di%20scavare%20se%20esiste%20gi%C3%A0%20una%20soluzione%20per%20sotto%20problema.%3C%2FP%3E%3CP%3ED%C3%AC%20che%20ho%3C%2FP%3E%3CP%3E%3CSTRONG%3ETabella%20A%20con%203%20colonne%20%22X%22%2C%20%22Y%22%20e%20%22Z%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3ETabella%20B%20con%201%20colonna%20%22R%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ELa%20colonna%20R%20contiene%20gi%C3%A0%20alcuni%20valori%20di%20X%20e%20Y.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EVoglio%20unire%20la%20Tabella%20A%20e%20la%20Tabella%20B%20come%20---%26gt%3B%20Colonna%20di%20corrispondenza%20(X%20%3D%20R)%20OR%20Colonna%20di%20corrispondenza%20(Y%20%3D%20R)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EPu%C3%B2%20essere%20fatto%20con%20JSL%20o%20qualcosa%20del%20genere%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EGrazie%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eariete%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%3ERi%3A%20JMP%20Join%20Tables%20con%202%20o%20pi%C3%B9%20colonne%20corrispondenti%20a%20Logical%20%22OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-50875%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3EGrazie%20Jim.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-50475%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERi%3A%20JMP%20Join%20Tables%20con%202%20o%20pi%C3%B9%20colonne%20corrispondenti%20a%20Logical%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%3EIo%20posso%20realizzare%20quello%20che%20vuoi%20con%202%20semplici%20join%20....%20vedi%20il%20mio%20script%20dove%20non%20si%20sa%20se%20un%20nome%20in%20una%20tabella%20di%20dati%20%C3%A8%20il%20nome%20o%20il%20cognome%20nell'altra%20tabella%20di%20dati%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.