CODEWITHSUNDEEP
kdb+
codeforkdb
codeforkdb

Reputation: 13

map values based on condition in kdb

I have a set of trade data like the following:

date sym asset points tenor
2020.11.10 ABC FX 0.2 "AN"
2020.11.15 ABB DX 0.1 "AN"
2020.11.17 AAA FX 0.1 "ON"
2020.11.1 ABB FX 0.3 "AN"
2020.11.1 AAA FX 0.1 "SW"

for the asset=FX the tenor has to mapped/updated with below:

"AN"->"AN/3S"

"ON"->"0N"

"SW"->"1W"

The result should look like:

date sym asset point tenor
2020.11.10 ABC FX 0.2 "AN/3s"
2020.11.15 ABB DX 0.1 "AN"
2020.11.17 AAA FX 0.1 "0N"
2020.11.1 ABB FX 0.3 "AN/3s"
2020.11.1 AAA FX 0.1 "1W"

I have tried the naive approach already. Is there a way to do it programmatically? For reference the table name is data and it is partitioned on date

Upvotes: 0

Views: 714

Answers (2)

SeanHehir
SeanHehir

Reputation: 1593

We can create a set of indices for the tenors we want to update using the find (?) operator. We then use those indices to index into our corresponding map.

Finally we fill forward with tenor in case there were any tenors not covered in our mapping (you could replace this with a suitable fill of your choice).

The where clause ensures we only perform this operation on FX assets. As it seems your are working with a date partitioned table I have also added a date clause to further constrain the query

q)update ("AN/3S";"0N";"1W") ("AN";"ON";"SW")?tenor from select from data where date within 2020.11.01 2020.11.20,asset = `FX
date       sym asset point tenor
----------------------------------
2020.11.10 ABC FX    0.2   "AN/3S"
2020.11.17 AAA FX    0.1   "0N"
2020.11.01 ABB FX    0.3   "AN/3S"
2020.11.01 AAA FX    0.1   "1W"

Upvotes: 1

Anton Dovzhenko
Anton Dovzhenko

Reputation: 2569

Try below query, where t is the table:

update tenor: (`AN`ON`SW!`$("AN/3S";"0N";"1W"))@/:tenor from t

It looks up dictionary `AN`ON`SW!`$("AN/3S";"0N";"1W") for each tenor on the right.

Upvotes: 1

Related Questions