Choose Language Hide Translation Bar
Highlighted
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
Highlighted
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
Highlighted
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

Article Labels

    There are no labels assigned to this post.