Any quick ways to glue tables side by side?

Hi there,

I am wondering that if there is a verb or something that could glue two tables side by side? I notice that some verbs allow to join columns with different column names side by side, but fail to do that when the column names are same.  

Here is an example: firstly, there are 2 original tables, say sp and sp1

s p qty s p1 qty1

--------- ----------

s1 p1 300 s1 p1 300

s1 p2 200 s1 p2 200

s1 p3 400 s1 p3 400

s1 p4 200 s1 p4 200

s4 p5 100 s4 p5 100

s1 p6 100 s1 p6 100

s2 p1 300 s2 p1 300

s2 p2 400 s2 p2 400

s3 p2 200 s3 p2 200

s4 p2 200 s4 p2 200

s4 p4 300 s4 p4 300

s1 p5 400 s1 p5 400

is there a verb or something that can join the two tables side by side and return the following result?  To go one step further, what if sp and sp1 are not of equal length?

s  p  qty s  p1 qty1

--------------------

s1 p1 300 s1 p1 300

s1 p2 200 s1 p2 200

s1 p3 400 s1 p3 400

s1 p4 200 s1 p4 200

s4 p5 100 s4 p5 100

s1 p6 100 s1 p6 100

s2 p1 300 s2 p1 300

s2 p2 400 s2 p2 400

s3 p2 200 s3 p2 200

s4 p2 200 s4 p2 200

s4 p4 300 s4 p4 300

s1 p5 400 s1 p5 400



Any help and suggestions are sincerely appreciated!




Thanks,


Xinyu

Hi,

You can use sp,'sp1 to join the tables:

q)sp,'sp1

s p qty p1 qty1

-----------------

s1 p1 300 p1 300

s1 p2 200 p2 200

Although only one s column is kept as the second overwrites the first. You would need to rename it in order to keep it:

q)sp,'(`s1 xcol sp1)

s p qty s1 p1 qty1

--------------------

s1 p1 300 s1 p1 300

s1 p2 200 s1 p2 200


For tables of different lengths lj may be used, but the right argument must be a keyed table:

q)sp lj sp1 //unkeyed

k){$[$[99h=@t:v y;98h=@. t;0];[n:#+!y;n!x 0!y];x y]}

'mismatch


q)sp lj (`s xkey sp1) //keyed

s p qty p1 qty1

-----------------

s1 p1 300 p1 300

s1 p2 200 p1 300

This is because the tables are joined by the keyed column. The keyed columns also exist in the left hand table, as seen below:

q)sp lj (s xkey s1 xcol sp1)

k){(x,f@&~(f:cols y)in x)#y}

's

It also maybe of note that a quick way to key a table is to use !, eg:

q)sp lj 1!sp1

s p qty p1 qty1

-----------------

s1 p1 300 p1 300

s1 p2 200 p1 300


q)1!sp1

s | p1 qty1

–| -------

s1| p1 300

s1| p2 200

Hope this helps!

Regards,

Thomas Smyth

AquaQ Analytics

Thomas answered the second well. 

For the first, I’ve never had a use case for this but you could achieve the result by simply creating dictionary and flipping it i.e. q)flip(combined cols!combined values).

q){flip (!) . raze each(key each x;value each x)}flip each(sp;sp1)

s  p  qty s  p  qty1


s1 p1 300 s1 p1 300 

s1 p2 200 s1 p2 200 

s1 p3 400 s1 p3 400 

s1 p4 200 s1 p4 200 

s4 p5 100 s4 p5 100 

s1 p6 100 s1 p6 100 

s2 p1 300 s2 p1 300 

s2 p2 400 s2 p2 400 

s3 p2 200 s3 p2 200 

s4 p2 200 s4 p2 200 

s4 p4 300 s4 p4 300 

s1 p5 400 s1 p5 400 

Is that what you were looking for?

Thanks,

Michael

Hi,

Many thanks for the detailed answer! 

Though some modifications are needed, " ,' " is basically what I was looking for.  Inimitable simplicity!  Thanks!

Best,

Xinyu 

Hi Michael,

Thanks for the reply!

Honestly I’ve never had a use case for this either. I noticed that I could manipulate rows fairly easy in KDB, so a thought came into my mind that whether I could do similar manipulation with columns. 

Best,

Xinyu