CODEWITHSUNDEEP
kdb+
Utsav
Utsav

Reputation: 5918

Utility like except for tables in kdb

As we have except function for lists in kdb to find the elements which are present in one list and not in another, similarly do we have any utility to extract the rows present in one table and not in another based on a column?

Eg: I have two tables:

l:([]c1:`a`b`c`d;c2:10 20 30 40)
r:([]c1:`a`a`a`b`b;c3:100 200 300 400 50)

Since, for column c1 in table l we have row c d which are not present in column c1 of table r.
Do we have any utility in kdb which can be used to get output like below?

c1 c2
-----
c  30
d  40

I got the output using - 

select from l where c1 in l[`c1] except r`c1

But, I'm searching for better/optimised solution/utility to get the same output.

Upvotes: 0

Views: 1298

Answers (2)

Matt Moore
Matt Moore

Reputation: 2800

A more functional form would be this:

{cl:cols[x]inter cols y;x where not(cl#x)in cl#y}[l;r]
c1 c2
-----
c  30
d  40

This should work if you don't know the columns to match on because of cols[x] inter cols[y] at the start which obtains common cols between the two tables. It also works without columns being keyed.

Although in this specific case, the following would be a little bit faster:

l where not l[`c1] in r[`c1]

Upvotes: 1

terrylynch
terrylynch

Reputation: 13657

I don't think there's anything wrong with your current implementation but you could use drop (aka _) on a keyed table for a more succinct approach:

q)#[1#`c1;r]_1!l
c1| c2
--| --
c | 30
d | 40

This also remains pretty neat when they "key" is more than one column:

l0:([]c0:`x`y`z`w;c1:`a`b`c`d;c2:10 20 30 40)
r0:([]c0:`y`x`x`x`y;c1:`a`a`a`b`b;c3:100 200 300 400 50)

q)#[`c0`c1;r0]_2!l0
c0 c1| c2
-----| --
z  c | 30
w  d | 40

Upvotes: 3

Related Questions