cancel
Showing results for
Did you mean:

## Joining tables with lists of data

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
New Contributor
Try this

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

key1| val

----| -----

a   | 1 4 7

b   | 2 5 8

c   | 3 6 9

New Contributor
Hi Roni

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

Would do the trick for you

Kind regards
Sean

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
New Contributor

A keyed table is a dictionary mapping between a table of keys and a table of values, so a simple join will have upsert semantics.

`q)a,b,ckey1| val----| ---a   | 7 b   | 8 c   | 9 `

We may apply the join operation between the values of two dictionaries using each. We might ordinarily expect this to work, like:

`q)(`a`b`c!1 2 3),'(`a`b`c!4 5 6)a| 1 4b| 2 5c| 3 6`

However, the values of a keyed table are the rows of the value table, which are themselves dictionaries (remember a table is a list of dictionaries), so we still see upsert semantics.

`q)a,'b,'ckey1| val----| ---a   | 7 b   | 8 c   | 9  `

The key insight is that we need to further map the join-each operation onto the values of these row dictionaries:

`q)a,''b,''ckey1| val ----| -----a   | 1 4 7b   | 2 5 8c   | 3 6 9`

And with this, we can use over to apply this join-each-each operation to a list of keyed tables.

`q),''/[(a;b;c)]key1| val ----| -----a   | 1 4 7b   | 2 5 8c   | 3 6 9`

New Contributor III
Thanks very much all, thats helpful.