Joins That Exclude

[jamesh]

Using two hotfiles, I've created a new catalog over them.  In the catalog, I'm joining these hotfiles.  I'd like for the join to exclude Items (or key values) in the first hotfile that do not exist in the second hotfile.  I've tried a NE join, a NE with the "outside" box checked on the first, second and both files without success.

If such a join is not possible, I did see on the Filter tab, an option for a "not" operator on a dataset that's an existing .IMR report.  So then would I get the desired result if I were to create a report using the total pool of Items, then create another report with this "not in dataset..." opertor?

[MFGF]

Hi,

Unless you check the 'outer join' check boxes, a normal equi join (=) should give you what you need as long as your reports include data from both hotfiles (ie you will only end up with rows that have linking values in both tables).

Can it be that simple, or am I missing something obvious (which is quite normal with me! :-)

Regards,

MF.

[cognosfreelancer]

Create an equi join on the column that contains the key values.

Next convert it to an outer join on the second hotfile.

This way you will get rows that contain matchin key values as well as rows from the second hotfile that do not match key values from the first hotfile.

NKT

[jamesh]

Thank you for those replies.  I was a little unclear above.  The resulting data set should have in it only those values that exist in the first hotfile and do not exist in the second.  Before joining, there will be several values that are equal to each other in both hotfiles.

[jamesh]

Finally found the right combination to include all values from the first catalog that have no match in the second.  It's a three step process: 1) Create an eqijoin on the key values of the two catalogs; 2) Make the first catalog an outer join (this will inlcude all values from that catalog); 3) Using Filter, check for "Value2 is missing" (this eliminates instances where the value appears in both catalogs.)