topic Re: Is there a "true" outer-join in JMP? in Discussions

Is there a "true" outer-join in JMP?

Lavik17 — Thu, 08 Jun 2023 16:42:11 GMT

I am looking for a true outer-join that will return only the mis-matches between the tables based on the match column.

The dialogue allows for including non-matches to the inner match, but there's no option to just search for the non-matches and exclude the inner-match.

Re: Is there a "true" outer-join in JMP?

Jed_Campbell — Fri, 20 Jan 2023 21:41:53 GMT

I'm not aware of a direct way, but an indirect way is to choose the "Match Flag" option when creating the join, then you can manually/programmatically delete any rows that have the label "Both" in the "Match Flag" column. I've put a script below that does this as an example.

dt1 = open ("$SAMPLE_DATA/Hot Dogs.jmp");
dt2 = open ("$SAMPLE_DATA/Hot Dogs2.jmp");

dt1 << Delete rows (1); //to make an outer join possible
dt2 << delete rows (2); //to make an outer join possible

dtjoined = dt1 << Join(
	With( dt2 ),
	Match Flag( 1 ), //<-use this later
	By Matching Columns( :Product Name = :Product Name ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 1 ),
	Output Table( "joined" )
);

dtjoined << Select Where (:Match Flag == 3); //JMP uses column values for the text
dtjoined << delete rows ();

Re: Is there a "true" outer-join in JMP?

jthi — Fri, 20 Jan 2023 21:45:53 GMT

Is this the situation you are looking for?

You could enable Match Flag, perform full outer join and remove rows which have Match Flags "Both".

Re: Is there a "true" outer-join in JMP?

Lavik17 — Tue, 24 Jan 2023 17:43:02 GMT

Thank you, this would work!

Re: Is there a "true" outer-join in JMP?

Lavik17 — Tue, 24 Jan 2023 17:44:09 GMT

Thank you!

I wish there was an "outer join" option to check when doing a join operation.