Left Anti Join

[U_Waqar]

Hi Everyone,

Thanks in advance. I have a scenario with 2 tables as:

Take all the records from Table A and only take those records from Table B, which are not in Table A. (having one key as PolNo but also having other fields (not key fields) which need to show in report)

Table A:
Load * Inline [
PolNo
11
22
33
44
];

Table B:
Load * Inline [
Policy_No
11,
22,
55,
66
];

Expected Output:
PolNo
11 (from table A)
22 (from table A)
55 (from table B)
66 (from table B)

[Tanish]

Use of concatenate is not required, as you can see in the above response i have not used concatenate anywhere still im getting the result.

For more clarity, i have added 1 more column for you.

TableA:
Load *,
'A' as Flag;
Load * Inline [
PolNo , DESC
11,   11A
22  , 22A
33,  33A
44,  44A
];

TableB:
Load *
Where Not Exists(PolNo);
Load *,
'B' as Flag;
Load * Inline [
PolNo , DESC
11,    11B
22,   22B
55,   55B
66,   66B
];

Record with suffix A is from TableA and records with suffix B is from TableB.

[barnabyd]

G'day @U_Waqar,

If 11, 22, 33, 44, 55, 66 the expected outcome then the code is much simpler. You could use an outer join as suggested by @Aditya_Chitale but I prefer to concatenate selected rows from TableB into TableA (I think @Tanish's solution is pretty close). Here's my version:

concatenate (TableA)
load Policy_No as PolNo, <other B fields>
resident TableB where not exists( PolNo, Policy_No );
drop table TableB;

(Also, the 'P' in my previous script should have been 'PolNo' - sorry)

Cheers, Barnaby.