antijoin or set intersection

Community Support
1

I would like to perform something like an antijoin or set difference but I am not sure how to go about doing so.
The goal being a table where the keys from table B are excluded from table A.

Consider the following tables

A: ([ person: 1 1 1 2 2 2 3 3 3; date: 2015.12.20 2015.12.21 2015.12.22 2015.12.20 2015.12.21 2015.12.22 2015.12.20 2015.12.21 2015.12.22] qty: 10 5 20 30 5 10 5 10 10)

person    date    qty
1      2015.12.20 10
1      2015.12.21 5
1      2015.12.22 20
2      2015.12.20 30
2      2015.12.21 5
2      2015.12.22 10
3      2015.12.20 5
3      2015.12.21 10
3      2015.12.22 10

B:([ person: 1 1 1 2 2 2; date: 2015.12.20 2015.12.21 2015.12.22 2015.12.20 2015.12.21 2015.12.22] qty: 10 5 20 30 5 10)

person    date    qty
3      2015.12.20 5
3      2015.12.21 10
3      2015.12.22 10

the result should then be

antijoin[A; B]

person    date    qty
1      2015.12.20 10
1      2015.12.21 5
1      2015.12.22 20
2      2015.12.20 30
2      2015.12.21 5
2      2015.12.22 10

Does anybody have some clues as to how to pull this off.

2

select from A where not key[A] in key B<o:p></o:p>

<o:p> </o:p>

(PS: your B picture and final result don’t match the B definition you have)<o:p></o:p>

<o:p> </o:p>

From: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] On Behalf Of Brandon Ogle
Sent: Wednesday, December 9, 2015 2:06 PM
To: Kdb+ Personal Developers
Subject: [personal kdb+] antijoin or set intersection<o:p></o:p>

<o:p> </o:p>

I would like to perform something like an antijoin or set difference but I am not sure how to go about doing so.
The goal being a table where the keys from table B are excluded from table A.

Consider the following tables<o:p></o:p>

A: ([
     person: 1 1 1 2 2 2 3 3 3;
     date: 2015.12.20 2015.12.21 2015.12.22 2015.12.20 2015.12.21 2015.12.22 2015.12.20 2015.12.21 2015.12.22]
     qty: 10 5 20 30 5 10 5 10 10
)<o:p></o:p>

person    date    qty
1      2015.12.20 10
1      2015.12.21 5
1      2015.12.22 20
2      2015.12.20 30
2      2015.12.21 5
2      2015.12.22 10
3      2015.12.20 5
3      2015.12.21 10
3      2015.12.22 10<o:p></o:p>

B:([
     person: 1 1 1 2 2 2;
     date: 2015.12.20 2015.12.21 2015.12.22 2015.12.20 2015.12.21 2015.12.22]
     qty: 10 5 20 30 5 10
)<o:p></o:p>

person    date    qty
3      2015.12.20 5
3      2015.12.21 10
3      2015.12.22 10

the result should then be<o:p></o:p>

antijoin[A; B]<o:p></o:p>

person    date    qty
1      2015.12.20 10
1      2015.12.21 5
1      2015.12.22 20
2      2015.12.20 30
2      2015.12.21 5
2      2015.12.22 10

Does anybody have some clues as to how to pull this off.<o:p></o:p>

–
Submitted via Google Groups

3

On Wednesday, December 9, 2015 at 5:12:37 PM UTC-5, David Demner wrote

 

(PS: your B picture and final result don’t match the B definition you have)

Ah sorry about that! poor copy pasting on my part :(

Here is the correction for longevity:

A: ([
     person: 1 1 1 2 2 2 3 3 3;
     date: 2015.12.20 2015.12.21 2015.12.22 2015.12.20 2015.12.21 2015.12.22 2015.12.20 2015.12.21 2015.12.22]
     qty: 10 5 20 30 5 10 5 10 10
)

person    date    qty
1      2015.12.20 10
1      2015.12.21 5
1      2015.12.22 20
2      2015.12.20 30
2      2015.12.21 5
2      2015.12.22 10
3      2015.12.20 5
3      2015.12.21 10
3      2015.12.22 10

B:([
     person: 1 1 1 2 2 2;
     date: 2015.12.20 2015.12.21 2015.12.22 2015.12.20 2015.12.21 2015.12.22]
     qty: 10 5 20 30 5 10
)

person    date    qty
1      2015.12.20 10
1      2015.12.21 5
1      2015.12.22 20
2      2015.12.20 30
2      2015.12.21 5
2      2015.12.22 10

the result should then be

antijoin[A; B]

person    date    qty
3      2015.12.20 5
3      2015.12.21 10
3      2015.12.22 10
4

q)key[B]_A

person date      qty
3      2015.12.20 5 
3      2015.12.21 10
3      2015.12.22 10
5

Quite clever! Thanks for the responses, appears to do exactly what I wanted.