Update column value based on mapping table

Question

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:

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

Providing some sample input and output using q code instead of images would help a lot here. It's not clear to me what you want output. — Cathal O'Neill
– Cathal O'Neill, Commented Sep 25, 2024 at 10:40
edited, thanks. The quantity columns should display the value from the column thats listed — kka
– kka, Commented Sep 25, 2024 at 10:51

rianoc · Accepted Answer · 2024-09-25 10:59:02Z

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

Collectives™ on Stack Overflow

Update column value based on mapping table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related