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