CODEWITHSUNDEEP
kdb+
coderinq
coderinq

Reputation: 15

Add condition to where clause in q/kdb+

Table Tab

minThreshold maxThreshold point
1000 10000 10

wClause,:enlist((';~:;<);`qty;Tab[`minThreshold])

trying to incorporate maxThreshold column to where clause

qty >= MinThreshold
qty <= MaxThreshold

something like wClause,:enlist((';~:;<);`qty;Tab[`minThreshold]);Tab[`maxThreshold])

Upvotes: 0

Views: 910

Answers (2)

SJT
SJT

Reputation: 1097

Is your problem

  1. you have a Where phrase that works for functional qSQL and you want to extend it?
  2. you want to select rows of a table where the value of a quantity falls within an upper and lower bound?

If (2) you can use Join Each to get the bounds for each row, and within to test the quantity.

q)show t:([]lwr:1000 900 150;upr:10000 25000 500;qty:10 1000 450)
lwr  upr   qty
---------------
1000 10000 10
900  25000 1000
150  500   450
q)select from t where qty within' lwr{x,y}'upr
lwr upr   qty
--------------
900 25000 1000
150 500   450

Above we use {x,y} because in qSQL queries comma does not denote Join.

Upvotes: 0

rianoc
rianoc

Reputation: 3651

q)Tab:([] minThreshold:500 1000;maxThreshold:700 2000;point:5 10)
q)Tab
minThreshold maxThreshold point
-------------------------------
500          700          5
1000         2000         10
q)select from Tab where minThreshold>=900,maxThreshold<=2500
minThreshold maxThreshold point
-------------------------------
1000         2000         10
q)parse"select from Tab where minThreshold>=900,maxThreshold<=2500"
?
`Tab
,(((';~:;<);`minThreshold;900);((';~:;>);`maxThreshold;2500))
0b
()
q)?[Tab;((>=;`minThreshold;900);(<=;`maxThreshold;2500));0b;()]
minThreshold maxThreshold point
-------------------------------
1000         2000         10

See the whitepaper for more information on functional selects:

Upvotes: 1

Related Questions