Identify None Matching Data

[alwayslearning]

Hi,

I have several joined tables One master table and others which I use the data from too.

Table 1:

ID  Colour

1    Red

2    Orange

3    Purple

4    Blue

5    Brown

Left Join

Table 2:

ID   Name

1     John

5     Jane

I have many other columns of data and use a left join as I use data in both tables.  I want to identify, the amount of data in table 2 which is not in table 1.  i.e. identify that 2,3,4 are not in table 2 but in Table1 so 3 fields are missing would be my end goal here.

Thanks

[Chanty4u]

AM not sure but

table:

LOAD * INLINE [

ID , Colour

1 ,   Red

2  ,  Orange

3 ,   Purple

4 ,   Blue

5    ,Brown

];

Left Join

Table2:

LOAD * INLINE [

ID  , Name

1    , John

5 ,    Jane

]

Where not  Exists(ID) ;

[MayilVahanan]

Try like this

Table1:

LOAD * INLINE [

    ID,Colour

    1,Red

    2,Orange

    3,Purple

    4,Blue

    5,Brown

];

Table2:

LOAD * Inline

[

ID,Name

1, John

5,Jane

];

Left Join(Table1)

LOAD ID, 1 as flag Resident Table2;

Table3:

LOAD ID as MissingID Resident Table1 where flag <> 1;

[sunny_talwar · Accepted solution]

Create a flag while doing a left join

Table1:

LOAD ...

FROM Table1

Left Join (Table1)

LOAD *,

          1 as FlagTemp

FROM Table2;

Now you can create this:

FinalTable:

LOAD *,

          If(FlagTemp = 1, 1, 0) as Flag

Resident Table1;

DROP Table Table1;

DROP Field FlagTemp;

Now you have you flag where 1 is data available in Table2 and 0 where data isn't available.

[alwayslearning]

This has worked great.

Thanks everyone for your help