How to count number with multi-condition?

[hzadonis]

Hi, Dear Masters:

  I believe you must had met the issue:

  Say, we have a EOD table, which contains historical data. There's a field "pctchange" which means percent change. I want to count how many days the pctchange less than 0, more than 0 and equal 0 by each sym during a period.

  How stupid I am to count them one-by-one. Do you have smart method?

Thanks

hzadonis

[sohagan857]

signum is likely the operator you are looking for this test. will result in -1, 0 or 1 for your test not sure if you want running days, or just count days, but below is a count example q)n:100; t:([]date:n?.z.d;sym:n?`1;pct:(n?100)*n?-1 0 1) q)select count date by sym, signum pct from t sym pct| date -------| ---- a -1 | 1 a 0 | 6 a 1 | 1 b -1 | 1 b 0 | 4 b 1 | 4 ... // alternative layout with exec q)exec count@'`less`zero`more!(group signum pct)[-1 0 1] by sym from t | less zero more -| -------------- a| 1 6 1 b| 1 4 4 c| 1 0 2 d| 2 0 5 ... HTH, Sean

[hzadonis]

Hi, Sean,  Thank you! It works and smarter than my method.

在 2019年5月17日星期五 UTC+8下午4:42:01，Sean O'Hagan写道：

signum is likely the operator you are looking for this test. will result in -1, 0 or 1 for your test

not sure if you want running days, or just count days, but below is a count example

q)n:100; t:([]date:n?.z.d;sym:n?`1;pct:(n?100)*n?-1 0 1)
q)select count date by sym, signum pct from t
sym pct| date
-------| ----
a   -1 | 1
a   0  | 6
a   1  | 1
b   -1 | 1
b   0  | 4
b   1  | 4
...

// alternative layout with exec
q)exec count@'`less`zero`more!(group signum pct)[-1 0 1] by sym from t
 | less zero more
-| --------------
a| 1    6    1
b| 1    4    4
c| 1    0    2
d| 2    0    5
...

HTH,
Sean