0

I have 2 tables with the same columns. however, one table contains BUY data while the other contains SELL data. The first column of the table is the ID, and the number of rows of the tables might not be the same. How do I add the values of the columns in 2 tables if the values of the ID are the same? For example, I want to add tableA and tableB and the output is tableC

tableA      tableB
ID  Qty     ID  Qty
ABC 100     ABC 90
XZY 100     TOM 60
TOM 100     ROB 40
AL  100

tableC
ID  Qty
ABC 190
XYZ 100
TOM 160
AL  100
ROB 40
duckman
  • 687
  • 1
  • 15
  • 30

3 Answers3

2

You can use pj in this case. To use pj the tables must be keyed for the join to work and the result returned will include the rows that matched the keyed table. See link at bottom of post.

q)tabA:([]ID:`ABC`XYZ`TOM`AL;Qty:100 100 100 100)
q)tabB:([]`ID`ABC`TOM`ROB;Qty:90 60 40 )
q)tabA pj 1!tabB
ID  Qty
-------
ABC 190
XYZ 100
TOM 160
AL  100
q)tabB pj 1!tabA
ID  Qty
-------
ABC 190
TOM 160
ROB 40

In your query you require all rows from both tables to be included, therefore you need to create a rack of all IDs and apply a pj across both tables to add them together. See below.

(pj/)((select distinct ID from tabA,select distinct from tabB);`ID xkey tabA;`ID xkey tabB)

Another way to go about this is:

q)(pj/)(distinct raze enlist[`ID]#/:(tableA;tableB);1!tableA;1!tableB)
ID  Qty
-------
ABC 190
XYZ 100
TOM 160
AL  100
ROB 40

https://code.kx.com/q/ref/pj/

Caitlin Galway
  • 141
  • 3
0

You can use a plus join to add the tables for matching IDs, then join on the IDs from table B that dont match with union join, or a simple comma join

q)tableA:([]ID:`ABC`XZY`TOM`AL;Qty:100 100 100 100)
q)tableB:([]ID:`ABC`TOM`ROB;Qty:90 60 40)
q)(tableA pj 1!tableB) , select from tableB where not ID in tableA`ID

https://code.kx.com/q/ref/pj/

Adam Bonham
  • 615
  • 4
  • 7
0

An alternative would be to convert your tables to dictionaries and add those. Dictionaries seem like the more natural structure for these tables as you've described them as key value pairs:

q)dA:`ABC`XZY`TOM`AL!100 100 100 100
q)dB:`ABC`TOM`ROB!90 60 40
q)dA+dB
ABC| 190
XZY| 100
TOM| 160
AL | 100
ROB| 40
G Harding
  • 23
  • 2