Exclude common data from a Table

[#1]

Hi Guys,

I have two tables A and B..I need to report on data i.e.A-B(the values which is only in A not common with B).How I can do this in the script?

Table A

ID	Name	Val
1	XXX	11
2	PQR	12
3	RST	13
4	FFF	14
5	GGG	16

TableB

ID	Name	Val
1	XXX	40
2	PQR	50
6	TTT	60
7	SSS	70
8	UUU	80

Final Table should be (A-B)

ID	Name	Value
3	RST	13
4	FFF	14
5	GGG	16

Thanks....

[Miguel_Angel_Baeyens · Accepted solution]

Hi Pooja,

Use a WHERE clause with the EXISTS() function

NotToLoadInA:
LOAD Name
FROM TableB;
TableA:
LOAD *
FROM TableA
WHERE NO EXISTS(Name);
DROP TABLE NotToLoadInA;

Or

SourceTableA:
LOAD * INLINE [
ID, NameA, Val
1, XXX, 11
2, PQR, 12
3, RST, 13
4, FFF, 14
5, GGG, 16
];

SourceTableB:
LOAD * INLINE [
ID, NameB, Val
1, XXX, 40
2, PQR, 50
6, TTT, 60
7, SSS, 70
8, UUU, 80
];

ResultTable:
NOCONCATENATE LOAD * 
RESIDENT SourceTableA
WHERE NOT EXISTS(NameB, NameA);

DROP TABLES SourceTableA, SourceTableB; 

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Thank You so much Miguel..it worked.