CODEWITHSUNDEEP
kdb+
Kareem
Kareem

Reputation: 889

Select partialy matching rows from two differernt table in KDB?

assume I have the following two tables

Table1 

symbol ric
--------------  
AAA    AATSA.AS   
AALB   AALB.AS  
AJAX   AJAX.AS  
AMG    AMG.AS

Table2

Title               Ticker
ARCELORMITTAL REG   AAA.AS  
TC EURO EQ          TEET.AS
HAL TRUST           HAL.AS 
TETRAGON FIN GROUP  TFG.AS

what is the query that I need in order to get all Table1.symbol that are partially matching with Table2.Ticker?

for example in the following two tables I should be getting AAA from Table1.symbol since it is partially matching with AAA.AS from Table2.Ticker

Thanks

Upvotes: 0

Views: 785

Answers (2)

nyi
nyi

Reputation: 3229

In cases when there is no demarcation on how to split the symbol to do the exact match, you might want to search it using like :

q)select from t1 where any each t2[`Ticker] like/:(string[symbol],\:"*")
symbol ric
---------------
AAA    AATSA.AS

Though it is not an ideal solution and should be avoided by understanding the data and splitting the symbols to use the joins as described in the accepted answer.

Upvotes: 0

Manish Patel
Manish Patel

Reputation: 4491

I assume the only logic here is that Table2.Ticker can be split by . and that can be matched against Table1.symbol.

First off, you can split Ticker:

q)update fragment:`$("." vs' string Ticker)[;0] from `Table2
`Table2
q)Table2
Title             Ticker  fragment
----------------------------------
ARCELORMITTAL REG AAA.AS  AAA
TC EURO EQ        TEET.AS TEET

Although I could have called fragment symbol instead I have it as a separate column for illustration. You also need the column fragment to join on on Table1:

q)update fragment:symbol from `Table1
`Table1
q)Table1
symbol ric      fragment
------------------------
AAA    AATSA.AS AAA
AALB   AALB.AS  AALB

So now we can either do a uj or lj - you should look these up on the reference pages. Just to use uj in this case to see the whole shebang:

q)(`fragment xkey Table1) uj (`fragment xkey Table2)
fragment| symbol ric      Title             Ticker
--------| -----------------------------------------
AAA     | AAA    AATSA.AS ARCELORMITTAL REG AAA.AS
AALB    | AALB   AALB.AS
TEET    |                 TC EURO EQ        TEET.AS

On a sidenote... Joining Bloomberg and RIC symbols is a nightmare :-) Ultimately you're going to have to have tables that join up ISINS, RICs and Bloomberg tickers bearing in mind RICs and tickers change over time.

Upvotes: 3

Related Questions