CODEWITHSUNDEEP
kdb+
Data_journey
Data_journey

Reputation: 45

how to pass an array into update query kdb

Hello I have the following code that I would like to find the optimal way of passing the array of any size without having to explicitly do it line by line:

the two arrays are coeffs and tickers. thank you

t:update Value:Value*coeffs[0] from (select from t)where Code in `$tickers[0];
t:update Value:Value*coeffs[1] from (select from t)where Code in `$tickers[1];
t:update Value:Value*coeffs[2] from (select from t)where Code in `$tickers[2];
t:update Value:Value*coeffs[3] from (select from t)where Code in `$tickers[3];
t:update Value:Value*coeffs[4] from (select from t)where Code in `$tickers[4];

Upvotes: 1

Views: 220

Answers (2)

nyi
nyi

Reputation: 3229

Another way you can get it done is using lj.

q)t:([] n:til 10; Value:1+til 10; Code:10#`a`b`c`d`e)
q)tickers:enlist each "abcdf"

Creating keyed table kt using the tickers and coeffs :

q)kt:([Code:`$tickers] coeffs:2 4 6 8 10 )
q)kt
Code| coeffs
----| ------
a   | 2
b   | 4
c   | 6
d   | 8
f   | 10

Now joining the t with kt 

q)t:t lj kt
q)t
n Value Code coeffs
-------------------
0 1     a    2
1 2     b    4
2 3     c    6
3 4     d    8
4 5     e
5 6     a    2
6 7     b    4
7 8     c    6
8 9     d    8
9 10    e

Updating the table t where we have non-null coeff values

q)update Value:Value*coeffs from t where not null coeffs
n Value Code coeffs
-------------------
0 2     a    2
1 8     b    4
2 18    c    6
3 32    d    8
4 5     e
5 12    a    2
6 28    b    4
7 48    c    6
8 72    d    8
9 10    e
  • With lj you will end up having an extra column coeffs which you might want to delete.
  • This is particularly useful when you have multiple mapping (tickers->coeffs , tickers->delta etc ) you just need to create one table with all mappings.

Upvotes: 1

Thomas Smyth
Thomas Smyth

Reputation: 5644

Assuming both arrays are of the same length then you could try create a dictionary of tickers to coeffs:

dict:(`$tickers)!coeffs

This can then be used in an update statement:

update Value:Value*1^dict[Code] from t

The 1^ is crucial here as indexing into dict with a non-existent key will return a null. This notation allows you to fill null values with 1 thus ensuring Value remains the same.

Upvotes: 2

Related Questions