kdb

Update column value based on mapping table

I have the following table

tab:([]exec_location_1:`PA1`DNS`DNS;quantity_s9:8 40 20;mf_quantity_s17:123 123 4;eq_quantity_s17:10 20 30)

and I need to update it with a new column quantity which is based on the following mapping: orderQuantityMap:`NEW`XRM`PA1`PA3`PA4`PA5`XRT`XRB`MF1`DNS!(`quantity_s9`quantity_s9`quantity_s9`quantity_s9`quantity_s9`quantity_s9`quantity_s9`quantity_s9`mf_quantity_s17`eq_quantity_s17);

Im guessing this needs to be done as a functional update hence tried the following:

![tab;();0b;(enlist `quantity)!enlist(orderQuantityMap;`exec_location_1)]

which gives me the following result: result

How do I update this so that the actual value of the column is pulled in to the quantity col?

Solution

  • This is a basic version:

    q)update quantity:{x y}'[select quantity_s9, mf_quantity_s17, eq_quantity_s17 from tab;
                         orderQuantityMap exec_location_1] from tab
exec_location_1 quantity_s9 mf_quantity_s17 eq_quantity_s17 quantity
--------------------------------------------------------------------
PA1             8           123             10              8
DNS             40          123             20              20
DNS             20          4               30              30

    More dynamic version:

    q)update quantity:{x y}'[(distinct value orderQuantityMap)#tab;
                         orderQuantityMap exec_location_1] from tab
exec_location_1 quantity_s9 mf_quantity_s17 eq_quantity_s17 quantity
--------------------------------------------------------------------
PA1             8           123             10              8
DNS             40          123             20              20
DNS             20          4               30              30

    Technically you don't even need the index and can just use @':

    q)update quantity:tab@'orderQuantityMap exec_location_1 from tab
exec_location_1 quantity_s9 mf_quantity_s17 eq_quantity_s17 quantity
--------------------------------------------------------------------
PA1             8           123             10              8
DNS             40          123             20              20
DNS             20          4               30              30