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)

[BrunPierre]

Hi @U_Waqar  simply do a Right Join as such

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

Right Join
[Table B]:
Load
Policy_No as PolNo
Inline [
Policy_No
11,
22,
55,
66
];

[U_Waqar]

Thanks for your response.

With simple right join i get matching records data from Table B whereas for matching records i want to get data from table and for unmatch records from Table B.

Also I want to keep the flag of both tables which later need to show in report 

[BrunPierre]

Is this not the result you expected?

[U_Waqar]

yes this is the expected output but the other columns data also should come from same tables as:

Expected Output:
PolNo
11 (all other columns/fields should be from table A)
22 (all other columns/fields should be from table A)
55 (all other columns/fields should be from table B)
66 (all other columns/fields should be from table B)

Take all the Policies (with other fields) from Table A and only take those Polices from Table B which are not in Table A.

[barnabyd]

G'day @U_Waqar,

In your original post, you say:

> Take all the records from Table A and only take those records from Table B, which are not in Table A.

According to that logic, you should get rows: 11, 22, 33, 44, 55, 66. If this is the case, then the answer is to concatenate with a "where not exists" on the key field.

What is the reason that you don't want 33 and 44? Do you only want rows from Table A that are also in Table B? If this is the case, then here's the logic:

TableC:
noconcatenate load PolNo, <other A fields>
resident TableA where exists( Policy_No, PolNo );

concatenate (TableC) load Policy_No as PolNo, <other B fields>
resident TableB where not exists( P, Policy_No );

I hope this helps.

Cheers, Barnaby.

[U_Waqar]

Thanks for your response @barnabyd  

Sorry my bad i forgot to mention 33,44 in expected output. The final table should be like:

Expected Output:
PolNo:
11 (all the other fields should be taken from table A only)
22 (all the other fields should be taken from table A only)
33 (all the other fields should be taken from table A only)
44 (all the other fields should be taken from table A only)
55 (all the other fields should be taken from table B only)
66 (all the other fields should be taken from table B only)

How can I achieve this output?

In your mentioned solution, what you are referring to "P"

resident TableB where not exists( P, Policy_No 

Thanks

[Aditya_Chitale]

@U_Waqar 

Simply use an outer join in your script:

A:
load
...
from [xyz.qvd];

outer join (A)

B:
load
...
from [abc.qvd];

Regards,

Aditya

[Tanish]

Hi @U_Waqar ,

Let me tell you the simplest way to achieve this output.

You want all the records from TableA and only unmatched record from TableB.

Try this out:

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

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

Output:

Flag indicate from which table records is coming.

Hope this will be helpful.

Thanks,

Tanish

[U_Waqar]

Its not working accurately. Its only giving records from Table A.

I am using concatenate btw table A and table B also with where not Exist logic while loading data from Table B.