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
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@'lesszeromore!(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
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@'lesszero`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