CODEWITHSUNDEEP
kdb+
Lawrence
Lawrence

Reputation: 23

KDB/Q Question on Parsing a string and concatenate

InstanceID OrderID Strategy Fills
NG1 jhbubuy-ClientName1-2022-07-01 VWAP 5000
NG2 nb8yvce-ClientName2-2022-07-01 POV 300
NG1 cebciube-ClientName3-2022-07-01 TWAP 1000

Hi guys have the table above as an example. I am beginner in KDB/Q so please bear with me. Any guidance would help.

1.) I am trying to parse OrderID by "-" and create a new column "ClientName" based on what I parsed on OrderID

InstanceID OrderID Strategy Fills ClientName
NG1 jhbubuy-ClientName1-2022-07-01 VWAP 5000 ClientName1
NG2 nb8yvce-ClientName124-2022-07-01 POV 300 ClientName124
NG1 cebciube-ClientNameABC-2022-07-01 TWAP 1000 ClientNameABC

2.) Create a new column by parsing OrderID then concatenate it with a separate column called InstanceID. So it creates a InstanceID_OrderID

InstanceID OrderID Strategy Fills InstanceID_OrderID
NG1 jhbubuy-ClientName1-2022-07-01 VWAP 5000 NG1-jhbubuy-2022-07-01
NG2 nb8yvce-ClientName124-2022-07-01 POV 300 NG2-nb8yvce-ClientName124-2022-07-01
NG1 cebciube-ClientNameABC-2022-07-01 TWAP 1000 NG1-ClientNameABC-2022-07-01

Upvotes: 0

Views: 370

Answers (1)

Anton Dovzhenko
Anton Dovzhenko

Reputation: 2569

To get desired table you should:

  1. Use vs operator to split OrderID by "-"
  2. Get second element of each sublist for ClientName
  3. Remove second element from each sublist, prepend InstanceID and join new list using sv operator for InstanceID_OrderID.

If the table has following structure:

t: flip`InstanceID`OrderID`Strategy`Fills!
  (`NG1`NG2`NG3;
  ("hbubuy-ClientName1-2022-07-01";
     "nb8yvce-ClientName2-2022-07-01";
     "cebciube-ClientName3-2022-07-01");
  `VWAP`POV`TWAP;
  5000 300 1000);

The next query does the job:

update
  ClientName: {("-" vs x) 1} each OrderID,
  InstanceID_OrderID: {x: "-" vs x; "-" sv enlist[y],(1#x),2_x}'[OrderID;string InstanceID]
from t

where

  1. anonymous function {("-" vs x) 1} splits OrderID by "-" and chooses index 1
  2. {x: "-" vs x; "-" sv enlist[y],(1#x),2_x} splits OrderID by "-", chooses 0 and 2+ indices (there is no remove by index operation in Q), prepends InstanceID and joins everything into "-" delimited string

Upvotes: 3

Related Questions