Right Join - How do I get the excluded records

[#1]

Hi,

I am using a right join on 2 tables and I'm getting the expected results.  However, I'm trying to work out how I can get the excluded results.  I've created a sample scenario to explain my query:

I have 2 tables as follows:

Table 1                                    Table 2

Code          Name                    Code              Name

111             John                      111                John

112             Paul                       112               Paul

113             George                  113               George

114             Pete                       115               Ringo

When I right join Table 2 to Table 1 I get the following, which is correct:

Table 1                                   

Code          Name                   

111             John

112             Paul                      

113             George                 

115             Ringo

What I would like to get is a table of what was excluded:

Table 1                                   

Code          Name                   

114             Pete

Can anyone advise me if and how this is possible?

Thanks.

[sunny_talwar · Accepted solution]

May be this

Table2:

LOAD Code

FROM...

Table1:

NoConcatenate

LOAD Code,

     Name

FROM

Where Not Exists(Code);

DROP Table Table2;

[Peter_Cammaert]

Not using a JOIN I guess. A JOIN throws stuff together that has at least one value in common (no values in common is to be avoided)

Try with a WHERE Not Exists() clause. You'll need either a field that uniquely identifies each individual records, or a composite key which you can create when you first load the data. Let's assume that the Code field can serve as Primary Key, then when you first load the data for Table 1 you can use something like:

Table1:

LOAD *

FROM ... (...)

WHERE Not Exists(Code);

If Table1 is loaded from a resident table, you cannot use this trick because the values of the two Code field instances will be merged into the same symbol table. Exists() looks into the field symbol table to check whether a value has already been loaded. As a result, all values will always exist and no records will be stored in Table1. You'll first have to rename the Code field in the Table1 source table.

[its_anandrjs]

Check this out and try to load table this way

T1:

LOAD * INLINE [

    Code, Name

    111, John

    112, Paul

    113, George

    114, Pete

];

T2:

Concatenate

LOAD * INLINE [

    Code, Name

    111, John

    112, Paul

    113, George

    115, Ringo

];

Left Join(T1)

LOAD Name ,Count(Code) as Freq Resident T1 Group By Name;

NoConcatenate

LOAD * Resident T1 Where Freq=1;

DROP Table T1;

[#5]

Hi Sunny,

Your answer worked perfectly on my sample data.  However, when I applied it to my real data it didn't work.  The table returns no entries and I don't know why.  The table has 5 columns but the scenario is the same.  There is a unique code which identifies each record.  Any suggestions why or what I should look out for?  Have I given you enough information?

Thanks,

Eamonn.

[sunny_talwar]

Where not exists works in a way that it checks for a value if it exits in a field, no matter which table we are looking at.... Do you think that may be the code exists in that field from another table?

[#7]

Hi Sunny,

I found that the format of the source was not consistent.  When I changed it the script code you gave me works.

Thanks for the help,

Eamonn.