CODEWITHSUNDEEP
kdb+
Utsav
Utsav

Reputation: 5918

Check existence of sub-table in a table in kdb

I have a table 't' and a sub-table 'st'.
If the values of subtable matches with that of table then we want to update the 't' table with value Y in new column named 'exists' else N. 

Table:
q)t:([] id:("ab";"cd";"ef";"gh";"ij"); refid:("";"ab";"";"ef";""); typ:`BUY`SELL`BUY`SELL`BUY)
q)t
id   refid typ
---------------
"ab" ""    BUY
"cd" "ab"  SELL
"ef" ""    BUY
"gh" "ef"  SELL
"ij" ""    BUY

subtable:
q)st:([] id:("ab";"cd"); typ:`BUY`SELL)
q)st
id   typ
---------
"ab" BUY
"cd" SELL

Desired Output:
id   refid typ  exists
----------------------
"ab" ""    BUY  Y
"cd" "ab"  SELL Y
"ef" ""    BUY  N
"gh" "ef"  SELL N
"ij" ""    BUY  N

Tried various things like any,each,right each, in - but could get desired result.

Upvotes: 1

Views: 499

Answers (3)

Thomas Smyth
Thomas Smyth

Reputation: 5644

Another alternative to the other answers is to extract the sub table columns from t (cols[st]#t)and check they are in st:

update exists:(cols[st]#t)in st from t
id   refid typ  exists
----------------------
"ab" ""    BUY  1
"cd" "ab"  SELL 1
"ef" ""    BUY  0
"gh" "ef"  SELL 0
"ij" ""    BUY  0

If you need to display the result as YN then you can make a slight modification to get the following:

update exists:`N`Y(cols[st]#t)in st from t
id   refid typ  exists
----------------------
"ab" ""    BUY  Y
"cd" "ab"  SELL Y
"ef" ""    BUY  N
"gh" "ef"  SELL N
"ij" ""    BUY  N

Upvotes: 2

Callum Biggs
Callum Biggs

Reputation: 1550

Rahul's answer is by far neater than what follows, but the following may be more performant, depending on your table sizes. By matching the columns in t to st (indexing and flipping) and determining a boolean of matching rows, passed into YN boolean dictionary we can produce the desired result. 

q)`N`Y (flip cols[st]!t[cols[st]]) in st
`Y`Y`N`N`N
q)update exists:(`N`Y (flip cols[st]!t[cols[st]]) in st) from t
id   typ  refid exists
----------------------
"ab" BUY  ""    Y
"cd" SELL "ab"  Y
"ef" BUY  ""    N
"gh" SELL "ef"  N
"ij" BUY  ""    N
q)\t:100000 update exists:(`N`Y (flip cols[st]!t[cols[st]]) in st) from t
446
q)\t:100000 update `N^exists from t lj `id`typ xkey update exists:`Y from st
856

Rahuls lj method may generalize to be faster once attributes are applied.

Edited as per Rahuls observations,

Upvotes: 2

Rahul
Rahul

Reputation: 3969

One option is to use left join (lj)

q) update `N^exists from t lj `id`typ xkey update exists:`Y from st

id   refid typ  exists
----------------------
"ab" ""    BUY  Y     
"cd" "ab"  SELL Y     
"ef" ""    BUY  N     
"gh" "ef"  SELL N     
"ij" ""    BUY  N

Here I have defined id and typ columns as keys for st table. Change the keys of table st to the columns you want to match with table t.

Upvotes: 2

Related Questions