topic Re: Left Anti Join in App Development

Left Anti Join

U_Waqar — Tue, 03 Oct 2023 07:33:00 GMT

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)

Re: Left Anti Join

BrunPierre — Tue, 03 Oct 2023 07:55:20 GMT

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
];

Re: Left Anti Join

U_Waqar — Tue, 03 Oct 2023 08:06:50 GMT

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

Re: Left Anti Join

BrunPierre — Tue, 03 Oct 2023 09:29:19 GMT

Is this not the result you expected?

Re: Left Anti Join

U_Waqar — Tue, 03 Oct 2023 11:44:31 GMT

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.

Re: Left Anti Join

barnabyd — Tue, 03 Oct 2023 23:17:48 GMT

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.

Re: Left Anti Join

U_Waqar — Wed, 04 Oct 2023 08:52:09 GMT

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

Re: Left Anti Join

Aditya_Chitale — Wed, 04 Oct 2023 06:11:23 GMT

@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

Re: Left Anti Join

Tanish — Wed, 04 Oct 2023 06:38:44 GMT

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

Re: Left Anti Join

U_Waqar — Wed, 04 Oct 2023 08:46:06 GMT

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.

Re: Left Anti Join

Tanish — Wed, 04 Oct 2023 10:44:22 GMT

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.

Re: Left Anti Join

barnabyd — Mon, 09 Oct 2023 00:06:17 GMT

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.