2

I have a list called 'A' as: 

     cont
     "aa"
     "bb"
      "cc"

I have a table called 'run' containing columns 

      first    second     third
      sad      random      "aa"
      happy    random1     "dd"

I have to select those rows from 'run' where the column third contains elements in list A. I'm very new to kdb and any help on how to do this is appreciated. Thanks

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36

1 Answers1

2

You need the keyword in which allows you to check if the values in one list appear in another:

q)show A:("aa";"bb";"cc")
"aa"
"bb"
"cc"
q)show run:([]f:("sad";"happy");s:("random";"random1");t:("aa";"dd"))
f       s         t
----------------------
"sad"   "random"  "aa"
"happy" "random1" "dd"
q)select from run where t in A
f     s        t
-------------------
"sad" "random" "aa"

If A is an unkeyed table, then you can pull the column cont out using A`cont:

q)show A:([]cont:("aa";"bb";"cc"))
cont
----
"aa"
"bb"
"cc"
q)select from run where t in A`cont
f     s        t
-------------------
"sad" "random" "aa"

When using in the output is a boolean list, equal in count to that of the argument on the left (1 2 3 in this case):

q)1 2 3 in 2 4 6
010b

Use of the where clause in the select statement filters the rows returned. Applying where to boolean list returns only the indices where it is true:

q)where 1 2 3 in 2 4 6
,1

And by extension, only these indices would be returned from the table.

It should also be noted that even though the table you have provided is only an example it contains the keyword first as a column header. Keywords as headers should be avoided if possible as they will cause unexpected behaviour.

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
  • I'm getting the following error using 'in' (incompatible lengths (different lengths of operands for synchronized operation or table columns lengths are not the same) –  Jan 15 '18 at 11:59
  • the table 'A' has some header name too. Is the error due to this? –  Jan 15 '18 at 12:00
  • Added an example for if `A` is a table, where you need to pull the column `cont` from it. Additionally the order of the arguments for `in` matters, the columns from the table should be the first argument (`third`) and the comparison list should be the second (`A`). – Thomas Smyth - Treliant Jan 15 '18 at 12:05
  • I generated A using selecting a column from another table. Further the column third has type C –  Jan 15 '18 at 12:05
  • The updated method should work, by using `select` the variable `A` above is a table. You can return a list instead of a table by using `exec col from tab` instead of `select`. – Thomas Smyth - Treliant Jan 15 '18 at 12:07