Hi Sam,
Example of splayed multi-column link:
q)/ Create splayed tables
q):/temp/a/t1/ set ([]a:1 2 3; b::/temp/a/sym?abc; c:10 20 30) :/temp/a/t1/
q):/temp/a/t2/ set ([]a2:1 2; b2::/temp/a/sym?ab; c2:100 200)
`:/temp/a/t2/
// this is the key part you were missing, finding row indices
q):/temp/a/t2/lnk set t1!(flip t1ab)?(flip t2a2b2)
:/temp/a/t2/lnk q).[:/temp/a/t2/.d;();,;lnk] :/temp/a/t2/.d
q)\l /temp/a
q) / meta and querying looks ok
q)meta t2
| c | t f a |
|---|---|
| a2 | j |
| b2 | s |
| c2 | j |
| lnk | i t1 |
q)update lnk.a, lnk.b from t2
a2 b2 c2 lnk a b
1 a 100 0 1 a
2 b 200 1 2 b
Notice the line I changed. When your trying something and it doesn’t work the best method to track down what’s happening is to go line by line or even statement by statement. If you run just (flip t1ab)?(flip t2a2b2) you can see that finds the row index in table t1 that matches them columns in t2.
Regards
Ryan
From : “Sam” <jain.samit@gmail.com>
Sent : Monday, December 17, 2012 10:40 PM
To : personal-kdbplus@googlegroups.com
Subject : [personal kdb+] linked multi-columns for splayed tables
I have a large database with splayed tables on disk. I need a way to join these tables for querying and reporting. Since the tables are splayed and partitioned, it is not possible to have primary/foreign keys, so no join is possible. the only alternative is creating linked columns. However, in my case, key is a multi-column. Is it possible to create linked columns for multi-columns? For example:
t1:(a:1 2 3;b:ab`c;c:10 20 30);
t2:(a2:1 2;b2:ab;c2:100 200);
I want to create a column in t2 that links t2[a2b2] to t1[ab]. This doesn’t work:
select t1link:t1!t1[a`b]?(a2;b2) from t2
–
Submitted via Google Groups