Extract Not Matched Records

[nihhalmca]

Hi All,

I want to extract not matched records from 2 table based on common column.

For Example:

Table 1

ID,  Amount

1,   100

2,  200

Table 2

ID, Amount

1,  100

2,  200

4,  400

Result Should be

Table

ID,  Amount

4,   400     

Regards,

NiHhal.

[JonnyPoole]

Is the purpose to create 1 table without duplication ?  Or are you trying to create 2 separate  tables with a common column field whose data never matches ?  

[nihhalmca]

For Example:

Table 1

ID,  Amount

1,   100

2,  200

Table 2

ID, Amount

1,  100

2,  200

4,  400

Result Should be

Table

ID,  Amount

4,   400         

[martynlloyd]

Try

Table1:

ID as Key1

...

TableX:

LOAD

ID,

Amount

From ...

Where Not Exists(Key1, ID)

;

[JonnyPoole · Accepted solution]

i added a 2nd reference to the ID fields which i dropped later . 

Table1:

LOAD * INLINE [

    ID, ID1, Amount

    1, 1, 100

    2, 2, 200

];

Outer join (Table1)

LOAD * INLINE [

    ID,ID2, Amount

    1, 1, 100

    2, 2, 200

    4, 4, 400

];

FinalTable:

NoConcatenate

Load *

Resident Table1

where ID1<>ID2;

drop table Table1;

drop Fields ID1,ID2;

[nihhalmca]

Thanks for reply Martyn,

I tried you example, it is giving 2 table.

I am expecting one table that should has not matched records.

Regards,

Nihhal.

[tresesco]

See attached sample qvw.

[ashfaq_haseeb]

Hi,

Try below

Table1:

Load * Inline [

ID,Amount

1,100

2,200];

Table2:

Load ID as ID1 ,Amount

where not Exists(ID, ID);

load * Inline [

ID, Amount

1,100

2,200

4,400

]

;

drop table Table1;

Regards

ASHFAQ