CODEWITHSUNDEEP
kdb+
bigO6377
bigO6377

Reputation: 1274

KDB: select rows based on value in one column being contained in the list of another column

very simple, silly question. Consider the following table:

tt:([]Id:`6`7`12 ;sym:`A`B`C;symlist:((`A`B`M);(`X`Y`Z);(`H`F`C)))

Id  sym symlist
---------------
6   A   `A`B`M
7   B   `X`Y`Z
12  C   `H`F`C

I would like to select all rows in tt where the element in sym is contained in the list symlist. In this case, it means just the first and third rows. However, the following query gives me a type error.

select from tt where sym in symlist
(`type)

Whats the proper way to do this? Thanks

Upvotes: 1

Views: 8065

Answers (2)

terrylynch
terrylynch

Reputation: 13572

In reponse to JPCs answer (couldn't format this as a comment)....

Type error possibly caused by applying "where" to a scalar boolean

q)(`a`b`c) in (`a`g`b;`u`i`o;`g`c`t)
0b
q)where (`a`b`c) in (`a`g`b;`u`i`o;`g`c`t)
'type

Also, the reason the {x in y} lambda doesn't cause the error is because the "in" is obscured and is not visible to the parser (parser doesn't look inside lambdas)

q)0N!parse"select from tt where {x in y}[sym;symlist]";
(?;`tt;,,({x in y};`sym;`symlist);0b;())

Whereas the parser can "see" the "in" in the first case

q)0N!parse"select from tt where sym in symlist";
(?;`tt;,,(in;`sym;`symlist);0b;())

I'm guessing the parser tries to do some optimisations when it sees the "in"

Upvotes: 1

JPC
JPC

Reputation: 1919

You want to use the ' (each-both) adverb, so that they "pair up" so to speak. Recall that sym is just list, and symlist is a list of lists. You want to check each element in sym with the respective sub-list in symlist. You do this by telling it to "pair up".

q)tt:([]id:6712; sym:`A`B`C; symlist:(`A`B`M;`X`Y`Z;`H`F`C))
q)select from tt where sym in'symlist
id   sym symlist
----------------
6712 A   A B M  
6712 C   H F C

It's not entirely clear to me why your query results in a type error, so I'd be interested in hearing other people's responses.

q)select from tt where sym in symlist
'type
in
`A`B`C
(`A`B`M;`X`Y`Z;`H`F`C)
q)select from tt where {x in y}[sym;symlist]
id sym symlist
--------------

Upvotes: 3

Related Questions