CODEWITHSUNDEEP
kdb+
marrowgari
marrowgari

Reputation: 427

Is there a way to iterate through the alphabet in kdb?

I'm writing a function to select all symbols from a database that start with the letter "A", followed by "B", up to the letter "Z". I have a dummy table set up with 3 characters that looks like the following...

t:([]symbol:`A`App`B`Bapp`C`Capp; price:104.3 124.3 134.2 103.4 402.7 209.8; ID:1 2 3 4 5 6)

along with a function to select from the table where the symbol looks like x...

fetch:{[x;y]select from x where symbol like y}

I'm then calling the function for each alphabet and upserting to a new table...

fetchedA:h (`fetch; `t; "A*")
fetchedB:h (`fetch; `t; "B*")
fetchedC:h (`fetch; `t; "C*")

New empty table along with upsert function...

newNormData:([]symbol:`$(); price:`float$(); ID:`int$())
newNorm:{[x] `newNormData upsert x}

h (`newNorm; fetchedA)
h (`newNorm; fetchedB)
h (`newNorm; fetchedC)

Instead of having to make 26 function calls for each server I'd like to make 1 call that iterates through each of the characters in the alphabet. What is the proper way to do this in kdb/q?

Upvotes: 1

Views: 779

Answers (2)

Rahul
Rahul

Reputation: 3969

.Q.a variable provides list of small characters and .Q.A provides list of capital characters. You can use those for iteration. There are couple of ways to do this:

  1. Simple iteration over characters and making call for each one. Less efficient as this makes many rpc calls.

      q)  fetchAll: raze{h (`fetch; `t;x}) each .Q.A,'"*"

  2. More efficient one as it makes only 1 rpc call. Using 'like' feature for alternative choices in pattern. For example below command will select symbols starting with A or B.

    Ex: select from table where symbol like "[A|B]*"

Below command will consider all characters.

    q)  h(`fetch; `t;"[",("|" sv enlist each .Q.A),"]*")

Also if your both tables and functions are on same server then you dont need different rpc calls to fetch symbol data first and then update newNorm table. You could do this in one call. One way is to define function on server and call that.

Function on server:

  q) fetchAndUpdNorm:{[x]`newNormData upsert fetch[t] x}

On Client:

  q)  h(`fetchAndUpdNorm;"[",("|" sv enlist each .Q.A),"]*")

Update based on Jonathon comment:

Alternative command for second example:

 q)  h(`fetch; `t;"[A-Z]*")

For last case:

 q)  h(`fetchAndUpdNorm;"[A-Z]*")

Upvotes: 3

Fiona Morgan
Fiona Morgan

Reputation: 520

I believe you are looking for fetch[t] each .Q.A,'"*". You can do this and upsert to the new table by running 

h({`newNormData upsert fetch[`t] x;}';.Q.A,'"*")

client side.

Alternatively, if you wish to upsert symbols that start only with a capital letter and have it in alphabetical order it may be simpler to execute

newNormData:`symbol xasc select from t where symbol like "[A-Z]*"

instead.

Upvotes: 6

Related Questions