How to find intersection of two tables column names?

[#1]

Hi,

I have two data tables with similar column names and like to write a script to find the overlap (intersection) of two column names and write the interesection into a new data table. I tried using Tables and join and did not work for me.

I appreciate your help. Thanks

[#2]

Here is a simple example of how to do this.  I am not sure of what the final combined data table is, so I guessed it had to be a concatenation.

names default to here(1);
dtA = Open("$SAMPLE_DATA\Big Class.jmp");
dtB = Open("$SAMPLE_DATA\Big Class Families.jmp");

dtACol = New Table("A Columns", private,
	New Column("A", character, values(dtA<<get column names))
);
dtBCol = New Table("B Columns", private,
	New Column("B", character, values(dtB<<get column names))
);

dtJoin = dtACol << Join( private,
	With( dtBCol ),
	By Matching Columns( :A = :B ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);

CommonColList = dtJoin:A << getvalues;

dtAsubset = dtA << subset( private, selected rows(0), columns(CommonColList));
dtBsubset = dtB << subset( private, selected rows(0), columns(CommonColList));

dtFinal = dtAsubset << Concatenate( dtBsubset );

[#3]

Thanks Jim. My data tables are not equal in number of rows and the final table should be the intersection of the the two similar columns. I have for instance 25 rows in table A and 25 rows in table B and the output should be the two rows that is not comon between similar columns.

[#4]

@AT, 

dtA = Open("$SAMPLE_DATA\Big Class.jmp");
dtB = Open("$SAMPLE_DATA\Big Class Families.jmp");

ACols = Associative Array(dtA << Get Column Names("String")); 
BCols = Associative Array(dtB << Get Column Names("String")); 
Intersection = ACols ;
Intersection << Intersect(BCols); 
Res = Intersection << Get Keys;

Can you work with the common columns being outside the data table and then defining a new table ? 

If so, look at the above example. 

[#5]

OK, I am now totally confused......can you please provide a couple of input data tables and the desired outcome data table?

[#6]

Thanks. I tried it and did not see output table. I have attached my two input data tables.

[#7 · Accepted solution]

Is this what you want?  Note the Match Flag value for the rows not found in both data tables.

Names Default To Here( 1 );
input = Data Table( "input" );
output = Data Table( "output" );

dtFinal = input << Join(
	With( Output ),
	Match Flag( 1 ),
	By Matching Columns( :boardno = :boardno ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 1 ),
	Merge same name columns( 1 )
);

[#8]

Thanks. I have attached the output. It is 25 rows with Match Flag 3 and only two with Match Flag 1. I was expecting only two rows with no match. What does Match Flag 1 and 3 mean? (freuqnecy?). 

[#9]

1=data came only from data table 1

2=data came only from data table 2

3=data came from both data tables(a match)

See page 288 in the Using JMP documentation

     Help==>Books==>Using JMP

[#10]

Thanks Jim.