Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- KX Community
- :
- Discussion Forums
- :
- kdb+ and q
- :
- Re: Joining tables with lists of data

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Joining tables with lists of data

Options

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

2021.02.22 10:20 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

2021.02.22 10:23 AM

Try this

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

key1| val

----| -----

a | 1 4 7

b | 2 5 8

c | 3 6 9

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

2021.02.22 10:27 AM

Hi Roni

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

Would do the trick for you

Kind regards

Sean

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

2021.02.22 11:10 AM

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

Cathal

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

2021.02.22 11:42 AM - last edited a month ago

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,c

key1| 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 4

b| 2 5

c| 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,'c

key1| 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,''c

key1| val

----| -----

a | 1 4 7

b | 2 5 8

c | 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 7

b | 2 5 8

c | 3 6 9

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

2021.02.22 12:43 PM

Thanks very much all, thats helpful.

Related Content

- Issue with flip in Table Joins in KX Academy
- Why creating a table from a tale results in a list of dictionaries? in kdb+ and q
- Issues with Outputs of Strings and Symbols in KX Academy
- formatting a table with @ (apply) to use lists as a format type (for example symbol "s" is "S") in kdb+ and q
- What is the role played by key columns in a keyed table [ query join/performance ] ? in kdb+ and q

Main Office Contacts

**EMEA**

Tel: +44 (0)28 3025 2242

**AMERICAS**

Tel: +1 (212) 447 6700

**APAC**

Tel: +61 (0)2 9236 5700

Useful Information

Resources

KX. All Rights Reserved.

KX and kdb+ are registered trademarks of KX Systems, Inc., a subsidiary of FD Technologies plc.