nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to find intersection of two tables column names?

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

Accepted answers

txnelson

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

All comments

txnelson

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

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.

uday_guntupalli

@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.

txnelson

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

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

Output.jmp

Input.jmp