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