cancel
Showing results for 
Search instead for 
Did you mean: 

Joining tables with lists of data

hoffmanroni
New Contributor III
Hi All, wondering if someone can help.

I have 3 tables with a column of lists.  I want to take the the tables and add them to the first tables list.

So for example

a:([key1:`a`b`c]val:(enlist 1;enlist 2;enlist 3))
b:([key1:`a`b`c]val:(enlist 4;enlist 5;enlist 6))
c:([key1:`a`b`c]val:(enlist 7;enlist 8;enlist 9))

and end result would be 

q)key1| val
----| ---
a   | 1 4 7
b   | 2 5 8
c   | 3 6 9

I'm sure its something easy here but didn't see it. 

Thanks!

5 REPLIES 5

zak
New Contributor
Try this

q),''/[(a;b;c)]

key1| val  

----| -----

a   | 1 4 7

b   | 2 5 8

c   | 3 6 9


sohagan857
New Contributor
Hi Roni

(,''/)(a;b;c)

Would do the trick for you

Kind regards
Sean


cathal_o_neill
New Contributor
Hello Roni,

This is one way to achieve the result you're looking for.

q)select val by key1 from raze 0!'(a;b;c)
key1| val
----| -----
a   | 1 4 7
b   | 2 5 8
c   | 3 6 9

Joining keyed tables only leaves the left table values. That is why I unkey with 0! before selecting all values by key1.

q)a,c
key1| val
----| ---
a   | 7
b   | 8
c   | 9
q)(0!a),0!c
key1 val
--------
a    1
b    2
c    3
a    7
b    8
c    9

Hope this helps,
Cathal

dflynch
New Contributor
Content-ID: <9D001EA1AC37DA4B934AB929885F9A1F@GBRP265.PROD.OUTLOOK.COM>Content-Transfer-Encoding: base64PGh0bWwgeG1sbnM6bz0idXJuOnNjaGVtYXMtbWljcm9zb2Z0LWNvbTpvZmZpY2U6b2ZmaWNlIiB4bWxuczp3PSJ1cm46c2NoZW1hcy1taWNyb3NvZnQtY29tOm9mZmljZTp3b3JkIiB4bWxuczptPSJodHRwOi8vc2NoZW1hcy5taWNyb3NvZnQuY29tL29mZmljZS8yMDA0LzEyL29tbWwiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy9UUi9SRUMtaHRtbDQwIj4NCjxoZWFkPg0KPG1ldGEgaHR0cC1lcXVpdj0iQ29udGVudC1UeXBlIiBjb250ZW50PSJ0ZXh0L2h0bWw7IGNoYXJzZXQ9dXRmLTgiPg0KPG1ldGEgbmFtZT0iR2VuZXJhdG9yIiBjb250ZW50PSJNaWNyb3NvZnQgV29yZCAxNSAoZmlsdGVyZWQgbWVkaXVtKSI+DQo8c3R5bGU+PCEtLQ0KLyogRm9udCBEZWZpbml0aW9ucyAqLw0KQGZvbnQtZmFjZQ0KCXtmb250LWZhbWlseTpDb3VyaWVyOw0KCXBhbm9zZS0xOjIgMCA1IDAgMCAwIDAgMCAwIDA7fQ0KQGZvbnQtZmFjZQ0KCXtmb250LWZhbWlseToiQ2FtYnJpYSBNYXRoIjsNCglwYW5vc2UtMToyIDQgNSAzIDUgNCA2IDMgMiA0O30NCkBmb250LWZhY2UNCgl7Zm9udC1mYW1pbHk6Q2FsaWJyaTsNCglwYW5vc2UtMToyIDE1IDUgMiAyIDIgNCAzIDIgNDt9DQovKiBTdHlsZSBEZWZpbml0aW9ucyAqLw0KcC5Nc29Ob3JtYWwsIGxpLk1zb05vcm1hbCwgZGl2Lk1zb05vcm1hbA0KCXttYXJnaW46MGNtOw0KCWZvbnQtc2l6ZToxMS4wcHQ7DQoJZm9udC1mYW1pbHk6IkNhbGlicmkiLHNhbnMtc2VyaWY7fQ0KYTpsaW5rLCBzcGFuLk1zb0h5cGVybGluaw0KCXttc28tc3R5bGUtcHJpb3JpdHk6OTk7DQoJY29sb3I6Ymx1ZTsNCgl0ZXh0LWRlY29yYXRpb246dW5kZXJsaW5lO30NCnNwYW4uRW1haWxTdHlsZTE5DQoJe21zby1zdHlsZS10eXBlOnBlcnNvbmFsLXJlcGx5Ow0KCWZvbnQtZmFtaWx5OiJDYWxpYnJpIixzYW5zLXNlcmlmOw0KCWNvbG9yOndpbmRvd3RleHQ7fQ0KLk1zb0NocERlZmF1bHQNCgl7bXNvLXN0eWxlLXR5cGU6ZXhwb3J0LW9ubHk7DQoJZm9udC1zaXplOjEwLjBwdDt9DQpAcGFnZSBXb3JkU2VjdGlvbjENCgl7c2l6ZTo2MTIuMHB0IDc5Mi4wcHQ7DQoJbWFyZ2luOjcyLjBwdCA3Mi4wcHQgNzIuMHB0IDcyLjBwdDt9DQpkaXYuV29yZFNlY3Rpb24xDQoJe3BhZ2U6V29yZFNlY3Rpb24xO30NCi0tPjwvc3R5bGU+DQo8L2hlYWQ+DQo8Ym9keSBsYW5nPSJFTi1JRSIgbGluaz0iYmx1ZSIgdmxpbms9InB1cnBsZSIgc3R5bGU9IndvcmQtd3JhcDpicmVhay13b3JkIj4NCjxkaXYgY2xhc3M9IldvcmRTZWN0aW9uMSI+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj48c3BhbiBzdHlsZT0ibXNvLWZhcmVhc3QtbGFuZ3VhZ2U6RU4tVVMiPllvdSBjYW4gdXNlIGpvaW4tZWFjaC1lYWNoLW92ZXI6PG86cD48L286cD48L3NwYW4+PC9wPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gc3R5bGU9Im1zby1mYXJlYXN0LWxhbmd1YWdlOkVOLVVTIj48bzpwPiZuYnNwOzwvbzpwPjwvc3Bhbj48L3A+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj48c3BhbiBzdHlsZT0iZm9udC1mYW1pbHk6Q291cmllcjttc28tZmFyZWFzdC1sYW5ndWFnZTpFTi1VUyI+cSlhOihba2V5MTpgYWBiYGNddmFsOihlbmxpc3QgMTtlbmxpc3QgMjtlbmxpc3QgMykpPG86cD48L286cD48L3NwYW4+PC9wPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gc3R5bGU9ImZvbnQtZmFtaWx5OkNvdXJpZXI7bXNvLWZhcmVhc3QtbGFuZ3VhZ2U6RU4tVVMiPnEpYjooW2tleTE6YGFgYmBjXXZhbDooZW5saXN0IDQ7ZW5saXN0IDU7ZW5saXN0IDYpKTxvOnA+PC9vOnA+PC9zcGFuPjwvcD4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxzcGFuIHN0eWxlPSJmb250LWZhbWlseTpDb3VyaWVyO21zby1mYXJlYXN0LWxhbmd1YWdlOkVOLVVTIj5xKWM6KFtrZXkxOmBhYGJgY112YWw6KGVubGlzdCA3O2VubGlzdCA4O2VubGlzdCA5KSk8bzpwPjwvbzpwPjwvc3Bhbj48L3A+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj48c3BhbiBzdHlsZT0iZm9udC1mYW1pbHk6Q291cmllcjttc28tZmFyZWFzdC1sYW5ndWFnZTpFTi1VUyI+cSksJycvWyhhO2I7YyldPG86cD48L286cD48L3NwYW4+PC9wPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gc3R5bGU9ImZvbnQtZmFtaWx5OkNvdXJpZXI7bXNvLWZhcmVhc3QtbGFuZ3VhZ2U6RU4tVVMiPmtleTF8IHZhbCZuYnNwOw0KPG86cD48L286cD48L3NwYW4+PC9wPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gc3R5bGU9ImZvbnQtZmFtaWx5OkNvdXJpZXI7bXNvLWZhcmVhc3QtbGFuZ3VhZ2U6RU4tVVMiPi0tLS18IC0tLS0tPG86cD48L286cD48L3NwYW4+PC9wPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gc3R5bGU9ImZvbnQtZmFtaWx5OkNvdXJpZXI7bXNvLWZhcmVhc3QtbGFuZ3VhZ2U6RU4tVVMiPmEmbmJzcDsmbmJzcDsgfCAxIDQgNzxvOnA+PC9vOnA+PC9zcGFuPjwvcD4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxzcGFuIHN0eWxlPSJmb250LWZhbWlseTpDb3VyaWVyO21zby1mYXJlYXN0LWxhbmd1YWdlOkVOLVVTIj5iJm5ic3A7Jm5ic3A7IHwgMiA1IDg8bzpwPjwvbzpwPjwvc3Bhbj48L3A+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj48c3BhbiBzdHlsZT0iZm9udC1mYW1pbHk6Q291cmllcjttc28tZmFyZWFzdC1sYW5ndWFnZTpFTi1VUyI+YyZuYnNwOyZuYnNwOyB8IDMgNiA5PG86cD48L286cD48L3NwYW4+PC9wPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gc3R5bGU9Im1zby1mYXJlYXN0LWxhbmd1YWdlOkVOLVVTIj48bzpwPiZuYnNwOzwvbzpwPjwvc3Bhbj48L3A+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj48c3BhbiBzdHlsZT0ibXNvLWZhcmVhc3QtbGFuZ3VhZ2U6RU4tVVMiPlRoaXMgd29ya3MgYmVjYXVzZSBlYWNoLWVhY2ggbWFwcyB0aGUgam9pbiBvcGVyYXRvciB0byB0aGUgdmFsdWVzIG9mIHRoZSB2YWx1ZSBjb2x1bW4sIGdldHRpbmcgYXJvdW5kIHRoZSB1c3VhbCB1cHNlcnQgc2VtYW50aWNzIGZvciBkaWN0aW9uYXJpZXMuPG86cD48L286cD48L3NwYW4+PC9wPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gc3R5bGU9Im1zby1mYXJlYXN0LWxhbmd1YWdlOkVOLVVTIj48bzpwPiZuYnNwOzwvbzpwPjwvc3Bhbj48L3A+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj48c3BhbiBzdHlsZT0iZm9udC1mYW1pbHk6Q291cmllcjttc28tZmFyZWFzdC1sYW5ndWFnZTpFTi1VUyI+cSkwTiEnJ2E7PG86cD48L286cD48L3NwYW4+PC9wPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gc3R5bGU9ImZvbnQtZmFtaWx5OkNvdXJpZXI7bXNvLWZhcmVhc3QtbGFuZ3VhZ2U6RU4tVVMiPiwxPG86cD48L286cD48L3NwYW4+PC9wPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gc3R5bGU9ImZvbnQtZmFtaWx5OkNvdXJpZXI7bXNvLWZhcmVhc3QtbGFuZ3VhZ2U6RU4tVVMiPiwyPG86cD48L286cD48L3NwYW4+PC9wPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gc3R5bGU9ImZvbnQtZmFtaWx5OkNvdXJpZXI7bXNvLWZhcmVhc3QtbGFuZ3VhZ2U6RU4tVVMiPiwzPG86cD48L286cD48L3NwYW4+PC9wPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gc3R5bGU9Im1zby1mYXJlYXN0LWxhbmd1YWdlOkVOLVVTIj48bzpwPiZuYnNwOzwvbzpwPjwvc3Bhbj48L3A+DQo8ZGl2IHN0eWxlPSJib3JkZXI6bm9uZTtib3JkZXItdG9wOnNvbGlkICNCNUM0REYgMS4wcHQ7cGFkZGluZzozLjBwdCAwY20gMGNtIDBjbSI+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj48Yj48c3BhbiBzdHlsZT0iZm9udC1zaXplOjEyLjBwdDtjb2xvcjpibGFjayI+RnJvbTogPC9zcGFuPjwvYj48c3BhbiBzdHlsZT0iZm9udC1zaXplOjEyLjBwdDtjb2xvcjpibGFjayI+Jmx0O3BlcnNvbmFsLWtkYnBsdXNAZ29vZ2xlZ3JvdXBzLmNvbSZndDsgb24gYmVoYWxmIG9mIFJvbmkgSG9mZm1hbiAmbHQ7aG9mZm1hbnJvbmlAZ21haWwuY29tJmd0Ozxicj4NCjxiPlJlcGx5IHRvOiA8L2I+JnF1b3Q7cGVyc29uYWwta2RicGx1c0Bnb29nbGVncm91cHMuY29tJnF1b3Q7ICZsdDtwZXJzb25hbC1rZGJwbHVzQGdvb2dsZWdyb3Vwcy5jb20mZ3Q7PGJyPg0KPGI+RGF0ZTogPC9iPk1vbmRheSAyMiBGZWJydWFyeSAyMDIxIGF0IDE4OjIwPGJyPg0KPGI+VG86IDwvYj5LZGIrIFBlcnNvbmFsIERldmVsb3BlcnMgJmx0O3BlcnNvbmFsLWtkYnBsdXNAZ29vZ2xlZ3JvdXBzLmNvbSZndDs8YnI+DQo8Yj5TdWJqZWN0OiA8L2I+W3BlcnNvbmFsIGtkYitdIEpvaW5pbmcgdGFibGVzIHdpdGggbGlzdHMgb2YgZGF0YTxvOnA+PC9vOnA+PC9zcGFuPjwvcD4NCjwvZGl2Pg0KPGRpdj4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxvOnA+Jm5ic3A7PC9vOnA+PC9wPg0KPC9kaXY+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj5IaSBBbGwsIHdvbmRlcmluZyBpZiBzb21lb25lIGNhbiBoZWxwLjxvOnA+PC9vOnA+PC9wPg0KPGRpdj4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxvOnA+Jm5ic3A7PC9vOnA+PC9wPg0KPC9kaXY+DQo8ZGl2Pg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+SSBoYXZlIDMgdGFibGVzIHdpdGggYSBjb2x1bW4gb2YgbGlzdHMuJm5ic3A7IEkgd2FudCB0byB0YWtlIHRoZSB0aGUgdGFibGVzIGFuZCBhZGQgdGhlbSB0byB0aGUgZmlyc3QgdGFibGVzIGxpc3QuPG86cD48L286cD48L3A+DQo8L2Rpdj4NCjxkaXY+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj48bzpwPiZuYnNwOzwvbzpwPjwvcD4NCjwvZGl2Pg0KPGRpdj4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPlNvIGZvciBleGFtcGxlPG86cD48L286cD48L3A+DQo8L2Rpdj4NCjxkaXY+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj48bzpwPiZuYnNwOzwvbzpwPjwvcD4NCjwvZGl2Pg0KPGRpdj4NCjxkaXY+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj5hOihba2V5MTpgYWBiYGNddmFsOihlbmxpc3QgMTtlbmxpc3QgMjtlbmxpc3QgMykpPG86cD48L286cD48L3A+DQo8L2Rpdj4NCjxkaXY+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj5iOihba2V5MTpgYWBiYGNddmFsOihlbmxpc3QgNDtlbmxpc3QgNTtlbmxpc3QgNikpPG86cD48L286cD48L3A+DQo8L2Rpdj4NCjwvZGl2Pg0KPGRpdj4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPmM6KFtrZXkxOmBhYGJgY112YWw6KGVubGlzdCA3O2VubGlzdCA4O2VubGlzdCA5KSk8bzpwPjwvbzpwPjwvcD4NCjwvZGl2Pg0KPGRpdj4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxvOnA+Jm5ic3A7PC9vOnA+PC9wPg0KPC9kaXY+DQo8ZGl2Pg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+YW5kIGVuZCByZXN1bHQgd291bGQgYmUmbmJzcDs8bzpwPjwvbzpwPjwvcD4NCjwvZGl2Pg0KPGRpdj4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxvOnA+Jm5ic3A7PC9vOnA+PC9wPg0KPC9kaXY+DQo8ZGl2Pg0KPGRpdj4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPnEpa2V5MXwgdmFsPG86cD48L286cD48L3A+DQo8L2Rpdj4NCjxkaXY+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj4tLS0tfCAtLS08bzpwPjwvbzpwPjwvcD4NCjwvZGl2Pg0KPGRpdj4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPmEmbmJzcDsgJm5ic3A7fCAxIDQgNzxvOnA+PC9vOnA+PC9wPg0KPC9kaXY+DQo8ZGl2Pg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+YiZuYnNwOyAmbmJzcDt8IDIgNSA4PG86cD48L286cD48L3A+DQo8L2Rpdj4NCjxkaXY+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj5jJm5ic3A7ICZuYnNwO3wgMyA2IDk8bzpwPjwvbzpwPjwvcD4NCjwvZGl2Pg0KPC9kaXY+DQo8ZGl2Pg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PG86cD4mbmJzcDs8L286cD48L3A+DQo8L2Rpdj4NCjxkaXY+DQo8cCBjbGFzcz0iTXNvTm9ybWFsIj5JJ20gc3VyZSBpdHMgc29tZXRoaW5nIGVhc3kgaGVyZSBidXQgZGlkbid0IHNlZSBpdC4mbmJzcDs8bzpwPjwvbzpwPjwvcD4NCjwvZGl2Pg0KPGRpdj4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxvOnA+Jm5ic3A7PC9vOnA+PC9wPg0KPC9kaXY+DQo8ZGl2Pg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+VGhhbmtzITxvOnA+PC9vOnA+PC9wPg0KPC9kaXY+DQo8ZGl2Pg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PG86cD4mbmJzcDs8L286cD48L3A+DQo8L2Rpdj4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPi0tIDxicj4NCllvdSByZWNlaXZlZCB0aGlzIG1lc3NhZ2UgYmVjYXVzZSB5b3UgYXJlIHN1YnNjcmliZWQgdG8gdGhlIEdvb2dsZSBHcm91cHMgJnF1b3Q7S2RiKyBQZXJzb25hbCBEZXZlbG9wZXJzJnF1b3Q7IGdyb3VwLjxicj4NClRvIHVuc3Vic2NyaWJlIGZyb20gdGhpcyBncm91cCBhbmQgc3RvcCByZWNlaXZpbmcgZW1haWxzIGZyb20gaXQsIHNlbmQgYW4gZW1haWwgdG8NCjxhIGhyZWY9Im1haWx0bzpwZXJzb25hbC1rZGJwbHVzK3Vuc3Vic2NyaWJlQGdvb2dsZWdyb3Vwcy5jb20iPnBlcnNvbmFsLWtkYnBsdXMrdW5zdWJzY3JpYmVAZ29vZ2xlZ3JvdXBzLmNvbTwvYT4uPGJyPg0KVG8gdmlldyB0aGlzIGRpc2N1c3Npb24gb24gdGhlIHdlYiB2aXNpdCA8YSBocmVmPSJodHRwczovL2VtZWEwMS5zYWZlbGlua3MucHJvdGVjdGlvbi5vdXRsb29rLmNvbS8/dXJsPWh0dHBzJTNBJTJGJTJGZ3JvdXBzLmdvb2dsZS5jb20lMkZkJTJGbXNnaWQlMkZwZXJzb25hbC1rZGJwbHVzJTJGYThmNzZjYjctZjJmYy00ZTdmLWFjMmItOGMxZDBmMDczOGY5biUyNTQwZ29vZ2xlZ3JvdXBzLmNvbSUzRnV0bV9tZWRpdW0lM0RlbWFpbCUyNnV0bV9zb3VyY2UlM0Rmb290ZXImYW1wO2RhdGE9MDQlN0MwMSU3QyU3QzI0NjZhYzhkZWRlNjQ3OGFmNTgwMDhkOGQ3NWU3OGY1JTdDODRkZjllN2ZlOWY2NDBhZmI0MzVhYWFhYWFhYWFhYWElN0MxJTdDMCU3QzYzNzQ5NjE0ODAzNzE2Njg3OSU3Q1Vua25vd24lN0NUV0ZwYkdac2IzZDhleUpXSWpvaU1DNHdMakF3TURBaUxDSlFJam9pVjJsdU16SWlMQ0pCVGlJNklrMWhhV3dpTENKWFZDSTZNbjAlM0QlN0MxMDAwJmFtcDtzZGF0YT1XWGVpM3lEM1N6JTJGRE1hWXFITm1CM1gwTE9nME8lMkZpRzI0aGI4ekc4cGVkUSUzRCZhbXA7cmVzZXJ2ZWQ9MCI+DQpodHRwczovL2dyb3Vwcy5nb29nbGUuY29tL2QvbXNnaWQvcGVyc29uYWwta2RicGx1cy9hOGY3NmNiNy1mMmZjLTRlN2YtYWMyYi04YzFkMGYwNzM4ZjluJTQwZ29vZ2xlZ3JvdXBzLmNvbTwvYT4uPGJyPg0KPGJyPg0KPG86cD48L286cD48L3A+DQo8L2Rpdj4NCjwvYm9keT4NCjwvaHRtbD4NCg

hoffmanroni
New Contributor III
Thanks very much all, thats helpful.