cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Rajat
Level IV

Join two tables by matching common column name

Hi,

I have two tables as below.

Table1 :

NameSectionSubject
AdamAMath
BobAscience
ChrisBenglish

 

Table2: 

 

NameSectionScore
AdamA80
BobA90
ChrisB84

 

I want to join both table in jsl and get the following output table but I don't want to give matching column names explicitly. Matching columns would be the columns with same column name in both table. Matching columns can vary. It can be 2 (as in shown example) or 3 or more but matching column would have same column name. 

output:

NameSectionSubjectScore
AdamAMath80
BobAscience90
ChrisBenglish84

 

Thanks :)

1 ACCEPTED SOLUTION

Accepted Solutions
david707
Level III

Re: Join two tables by matching common column name

This should do it:

 

DT1 = New Table("DT1",
	<< Add Rows(3),
	<< New Column("Name", character, Values({"Adam","Bob","Chris"})),
	<< New Column("Section", character, Values({"A","A","B"})),
	<< New Column("Subject", character, Values({"Math","Science","English"}))
);
DT2 = New Table("DT2",
	<< Add Rows(3),
	<< New Column("Name", character, Values({"Adam","Bob","Chris"})),
	<< New Column("Section", character, Values({"A","A","B"})),
	<< New Column("Score", numeric,continous, Values({80,90,84}))
);
LSTcols1 = DT1 << Get Column Names(string);
LSTcols2 = DT2 << Get Column Names(string);

STRjoinCols = "";

For(LVC=1,LVC<=N Items(LSTcols1),LVC++,
	STRcol = LSTcols1[LVC];
	If(
		Contains(LSTCols2,STRcol)
	,
		If(
			STRjoinCols == ""
		,
			STRjoinCols = ":"||STRcol||"==:"||STRcol
		,
			STRjoinCols = STRjoinCols||", :"||STRcol||"==:"||STRcol
		);
	);
);
expr = Eval Insert(
	"\[
	DT3 = DT1 << Join(
				With( DT2 ),
				Merge Same Name Columns,
				Match Flag( 0 ),
				By Matching Columns( ^STRjoinCols^ ),
				Drop multiples( 0, 0 ),
				Include Nonmatches( 0, 0 ),
				Preserve main table order( 0 ),
				Output Table( "DT3" )
	);
	]\"
);
Eval( Parse( expr ) );

View solution in original post

1 REPLY 1
david707
Level III

Re: Join two tables by matching common column name

This should do it:

 

DT1 = New Table("DT1",
	<< Add Rows(3),
	<< New Column("Name", character, Values({"Adam","Bob","Chris"})),
	<< New Column("Section", character, Values({"A","A","B"})),
	<< New Column("Subject", character, Values({"Math","Science","English"}))
);
DT2 = New Table("DT2",
	<< Add Rows(3),
	<< New Column("Name", character, Values({"Adam","Bob","Chris"})),
	<< New Column("Section", character, Values({"A","A","B"})),
	<< New Column("Score", numeric,continous, Values({80,90,84}))
);
LSTcols1 = DT1 << Get Column Names(string);
LSTcols2 = DT2 << Get Column Names(string);

STRjoinCols = "";

For(LVC=1,LVC<=N Items(LSTcols1),LVC++,
	STRcol = LSTcols1[LVC];
	If(
		Contains(LSTCols2,STRcol)
	,
		If(
			STRjoinCols == ""
		,
			STRjoinCols = ":"||STRcol||"==:"||STRcol
		,
			STRjoinCols = STRjoinCols||", :"||STRcol||"==:"||STRcol
		);
	);
);
expr = Eval Insert(
	"\[
	DT3 = DT1 << Join(
				With( DT2 ),
				Merge Same Name Columns,
				Match Flag( 0 ),
				By Matching Columns( ^STRjoinCols^ ),
				Drop multiples( 0, 0 ),
				Include Nonmatches( 0, 0 ),
				Preserve main table order( 0 ),
				Output Table( "DT3" )
	);
	]\"
);
Eval( Parse( expr ) );