I have a market data table with ordered price and quantity data, for a range of market providers and symbols, e.g.
b:([]symbol:();provider:();px1:();px2:();px3:();qty1:();qty2:();qty3:())
where px1 is top of book at quantity qty1, px2 next best price etc. Sample data may then be
`EURUSD;`EBS;1.1;1.2;1.3;1000000;2000000;4000000
I would like to collapse the px[n] and qty[n] columns into single columns, the format then being;
rb:([]symbol:();provider:();px:();qty:())
with the sample data then reading;
EURUSD, EBS, 1.1, 1000000
EURUSD, EBS, 1.2, 2000000
EURUSD, EBS, 1.3, 4000000
What would be the best approach to achieving this? As a newbie I was thinking along the lines of using dictionaries;
q)px:book `px1`px2`px3
q)qty:book `qty1`qty2`qty3
q)d:`px`qty!(px;qty)
q)flip d
px qty
-----------
1.1 1000000
1.2 2000000
1.3 4000000
... but I'm sure there are better ways.
You could use the following if you can't restructure your original table definition.
q)b
symbol provider px1 px2 px3 qty1 qty2 qty3
---------------------------------------------------
EURUSD EBS 1.1 1.2 1.3 1000000 2000000 4000000
EURUSD ECS 1.1 1.2 1.3 1000000 2000000 4000000
q)ungroup {rm _x,'flip enlist[y]!enlist flip x rm:cols[x]where cols[x] like string[y],"*" }/[b;\`px\`qty]
symbol provider px qty
---------------------------
EURUSD EBS 1.1 1000000
EURUSD EBS 1.2 2000000
EURUSD EBS 1.3 4000000
EURUSD ECS 1.1 1000000
EURUSD ECS 1.2 2000000
EURUSD ECS 1.3 4000000