2

I would like to join (concatenate) 2 or more, KDB tables which have repeating columns:

d1:`a`b`c!(1 2 3;4 5 6;7 8 9) 

d2:`a`b`c`a!(10 20 30;40 50 60;70 80 90;100 110 120)

t1: flip d1 
    a b c
    -----
    1 4 7
    2 5 8
    3 6 9


t2: flip d2
    a  b  c  a  
    ------------
    10 40 70 100
    20 50 80 110
    30 60 90 120

I am trying to concatenate the 2 tables, with nils where a table does not have a value, something like the following:

a  b  c  a  
------------
1  4  7    
2  5  8    
3  6  9    
10 40 70 100
20 50 80 110
30 60 90 120

t1 uj t2 yeilds (only has as many columns as t1 has defined):

a  b  c 
--------
1  4  7 
2  5  8 
3  6  9 
10 40 70
20 50 80
30 60 90

t2 uj t1 (repeats the entries from t1 where they are not available)

a  b  c  a  
------------
10 40 70 100
20 50 80 110
30 60 90 120
1  4  7  1  
2  5  8  2  
3  6  9  3

Is this possible? or would I need to pre-define a table which can accommodate all possible columns? Any advice would be appreciated.

Regards Clifford

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
cliff
  • 43
  • 1
  • 3

1 Answers1

3

Your second table has 2 columns with same name ('a') and that's giving the issue. rename it and uj will work.

q) t1:flip `a`b`c!(1 2 3;4 5 6;7 8 9) 

q) t2:flip `a`b`c`d!(10 20 30;40 50 60;70 80 90;100 110 120)
q) t1 uj t2

 a  b   c   d
 1  4   7   
 2  5   8   
 3  6   9   
 10 40  70  100
 20 50  80  110
 30 60  90  120
Rahul
  • 3,914
  • 1
  • 14
  • 25
  • Hi Rahul, unfortunately that's the problem I'm trying to solve. For a practical example, if you have a FIX message which has repeating groups (same tag is repeated 1 or more times). I am trying to import these messages into a table, where the columns map to the repeating tags. – cliff Jan 20 '17 at 10:43
  • 1
    well having multiple columns with same name is not a good design and cant expect kdb functions to behave normally. Better to preprocess your table and merge different cols with same tag(name) into one. Something like: raze each (,') . {value[x] group key x}@' (d1;d2) – Rahul Jan 20 '17 at 11:27
  • I tend to agree with you Rahul, was just looking for some insight from the kdb community, on how they'd suggest solving this. I will try your suggestion. Many thanks for your help – cliff Jan 21 '17 at 19:17
  • Rahul your suggestion works great for me. If that's not a testimony to the terseness of q, then I don't know what is. Thanks a ton! – cliff Jan 24 '17 at 09:27
  • glad it helped. – Rahul Jan 24 '17 at 10:39
  • Rahul, can I bother you with one follow on question, how do I apply your suggestion to more than 2 dictionaries? `raze each (,') . {value[x] group key x}@' (d1;d2;d3)` it breaks when concatenating using: `(,')` – cliff Feb 02 '17 at 09:16
  • You can use : (raze/)@' flip {value[x] group key x}@' (d1;d2;d3) – Rahul Feb 02 '17 at 11:49