KDB: select and round off each row
I created my own function of round off:
.q.rnd:{$[x < 0; -1; 1] * floor abs[x] + 0.5}
I have a table Test with a string column of COL
select "F"$(COL) from Test
24549.18741328
48939.50717263
-274853.33568872
-24549.18741328
298753.62574861
84822.70074144
-7468840.64371524
117944.21228603
-117944.21228603
7468840.64371524
-7468840.64371524
I want to derive a table that would round-off the records in Test
One would think that the statement below would work. But it does not.
select .q.rnd "F"$(COL) from Test
I get the error "type". So how do I round off the records?
kdb
asked Nov 22 at 21:21
user3682563
816
add a comment |
I created my own function of round off:
.q.rnd:{$[x < 0; -1; 1] * floor abs[x] + 0.5}
I have a table Test with a string column of COL
select "F"$(COL) from Test
24549.18741328
48939.50717263
-274853.33568872
-24549.18741328
298753.62574861
84822.70074144
-7468840.64371524
117944.21228603
-117944.21228603
7468840.64371524
-7468840.64371524
I want to derive a table that would round-off the records in Test
One would think that the statement below would work. But it does not.
select .q.rnd "F"$(COL) from Test
I get the error "type". So how do I round off the records?
kdb
asked Nov 22 at 21:21
user3682563
816
2
$is scalar conditional, but?is vector conditional. If you just used that, it would work.
– geocar
Nov 26 at 10:08
1
Also:?[x<0;-1;x>0;1;0]is calledsignum x
– geocar
Nov 26 at 10:08
1
Also: Don't name things beginning with.q
– geocar
Nov 26 at 10:10
add a comment |
I created my own function of round off:
.q.rnd:{$[x < 0; -1; 1] * floor abs[x] + 0.5}
I have a table Test with a string column of COL
select "F"$(COL) from Test
24549.18741328
48939.50717263
-274853.33568872
-24549.18741328
298753.62574861
84822.70074144
-7468840.64371524
117944.21228603
-117944.21228603
7468840.64371524
-7468840.64371524
I want to derive a table that would round-off the records in Test
One would think that the statement below would work. But it does not.
select .q.rnd "F"$(COL) from Test
I get the error "type". So how do I round off the records?
kdb
asked Nov 22 at 21:21
user3682563
816
I created my own function of round off:
.q.rnd:{$[x < 0; -1; 1] * floor abs[x] + 0.5}
I have a table Test with a string column of COL
select "F"$(COL) from Test
24549.18741328
48939.50717263
-274853.33568872
-24549.18741328
298753.62574861
84822.70074144
-7468840.64371524
117944.21228603
-117944.21228603
7468840.64371524
-7468840.64371524
I want to derive a table that would round-off the records in Test
One would think that the statement below would work. But it does not.
select .q.rnd "F"$(COL) from Test
I get the error "type". So how do I round off the records?
kdb
kdb
asked Nov 22 at 21:21
user3682563
816
asked Nov 22 at 21:21
user3682563
816
asked Nov 22 at 21:21
user3682563
816
asked Nov 22 at 21:21
user3682563
816
asked Nov 22 at 21:21
user3682563
816
816
2
$is scalar conditional, but?is vector conditional. If you just used that, it would work.
– geocar
Nov 26 at 10:08
1
Also:?[x<0;-1;x>0;1;0]is calledsignum x
– geocar
Nov 26 at 10:08
1
Also: Don't name things beginning with.q
– geocar
Nov 26 at 10:10
add a comment |
2
$is scalar conditional, but?is vector conditional. If you just used that, it would work.
– geocar
Nov 26 at 10:08
1
Also:?[x<0;-1;x>0;1;0]is calledsignum x
– geocar
Nov 26 at 10:08
1
Also: Don't name things beginning with.q
– geocar
Nov 26 at 10:10
2
2
$ is scalar conditional, but ? is vector conditional. If you just used that, it would work.– geocar
Nov 26 at 10:08
$ is scalar conditional, but ? is vector conditional. If you just used that, it would work.– geocar
Nov 26 at 10:08
1
1
Also:
?[x<0;-1;x>0;1;0] is called signum x– geocar
Nov 26 at 10:08
Also:
?[x<0;-1;x>0;1;0] is called signum x– geocar
Nov 26 at 10:08
1
1
Also: Don't name things beginning with
.q– geocar
Nov 26 at 10:10
Also: Don't name things beginning with
.q– geocar
Nov 26 at 10:10
add a comment |
2 Answers
2
active
oldest
votes
The result if the if-else conditional must be an atomic boolean. When you run .q.rnd on a column, you are operating on a list and x<0 is going to return a list of booleans, not an atom. The vector conditional is ?
Nonetheless, it looks like you want a resulting integer/long anyway, so just use parse here
q)t:(string (10?-1 1)*10?10000f)
q)select "F"$x from t
x
-------------------
4123.1701336801052
-9877.8444156050682
-3867.3530425876379
7267.8099689073861
4046.5459413826466
-8355.0649625249207
6427.3701561614871
-5830.2619284950197
1424.9352994374931
-9149.8820902779698
q)select "j"$"F"$x from t
x
-----
4123
-9878
-3867
7268
4047
-8355
6427
-5830
1425
-9150
answered Nov 22 at 21:52
Sean O'Hagan
1,544311
flooris faster than"j"$
– geocar
Nov 26 at 10:09
1
flooris faster than"j"$but"j"$is faster thansignum[x]*floor abs[x]+.5which is the comparable operation
– terrylynch
Nov 26 at 13:59
In other words"j"$has implicit rounding to nearest
– terrylynch
Nov 26 at 14:01
add a comment |
To add to what Sean's said, if you wanted to use your function as well you could use each which will apply .q.rnd to each item in the list.
q)select .q.rnd each "F"$x from t
x
-----
-3928
5171
5160
-4067
-1781
3018
-7850
5347
-7112
-4116
but using select "F"$x from t is better as it is vectorised.
q)t:1000 select "j"$"F"$x from t
22
q)t:1000 select .q.rnd each "F"$x from t
33
Also it should be noted that the .q namespace isn't necessary and is "reserved for kx use". A lot of the default q functions are in the .q namespace and there's always a chance future kdb updates could add a .q.rnd that has different behaviour and will break any code where you have used your function in.
answered Nov 23 at 10:22
O.Kaye
561
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53438114%2fkdb-select-and-round-off-each-row%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
The result if the if-else conditional must be an atomic boolean. When you run .q.rnd on a column, you are operating on a list and x<0 is going to return a list of booleans, not an atom. The vector conditional is ?
Nonetheless, it looks like you want a resulting integer/long anyway, so just use parse here
q)t:(string (10?-1 1)*10?10000f)
q)select "F"$x from t
x
-------------------
4123.1701336801052
-9877.8444156050682
-3867.3530425876379
7267.8099689073861
4046.5459413826466
-8355.0649625249207
6427.3701561614871
-5830.2619284950197
1424.9352994374931
-9149.8820902779698
q)select "j"$"F"$x from t
x
-----
4123
-9878
-3867
7268
4047
-8355
6427
-5830
1425
-9150
answered Nov 22 at 21:52
Sean O'Hagan
1,544311
flooris faster than"j"$
– geocar
Nov 26 at 10:09
1
flooris faster than"j"$but"j"$is faster thansignum[x]*floor abs[x]+.5which is the comparable operation
– terrylynch
Nov 26 at 13:59
In other words"j"$has implicit rounding to nearest
– terrylynch
Nov 26 at 14:01
add a comment |
The result if the if-else conditional must be an atomic boolean. When you run .q.rnd on a column, you are operating on a list and x<0 is going to return a list of booleans, not an atom. The vector conditional is ?
Nonetheless, it looks like you want a resulting integer/long anyway, so just use parse here
q)t:(string (10?-1 1)*10?10000f)
q)select "F"$x from t
x
-------------------
4123.1701336801052
-9877.8444156050682
-3867.3530425876379
7267.8099689073861
4046.5459413826466
-8355.0649625249207
6427.3701561614871
-5830.2619284950197
1424.9352994374931
-9149.8820902779698
q)select "j"$"F"$x from t
x
-----
4123
-9878
-3867
7268
4047
-8355
6427
-5830
1425
-9150
answered Nov 22 at 21:52
Sean O'Hagan
1,544311
flooris faster than"j"$
– geocar
Nov 26 at 10:09
1
flooris faster than"j"$but"j"$is faster thansignum[x]*floor abs[x]+.5which is the comparable operation
– terrylynch
Nov 26 at 13:59
In other words"j"$has implicit rounding to nearest
– terrylynch
Nov 26 at 14:01
add a comment |
The result if the if-else conditional must be an atomic boolean. When you run .q.rnd on a column, you are operating on a list and x<0 is going to return a list of booleans, not an atom. The vector conditional is ?
Nonetheless, it looks like you want a resulting integer/long anyway, so just use parse here
q)t:(string (10?-1 1)*10?10000f)
q)select "F"$x from t
x
-------------------
4123.1701336801052
-9877.8444156050682
-3867.3530425876379
7267.8099689073861
4046.5459413826466
-8355.0649625249207
6427.3701561614871
-5830.2619284950197
1424.9352994374931
-9149.8820902779698
q)select "j"$"F"$x from t
x
-----
4123
-9878
-3867
7268
4047
-8355
6427
-5830
1425
-9150
answered Nov 22 at 21:52
Sean O'Hagan
1,544311
The result if the if-else conditional must be an atomic boolean. When you run .q.rnd on a column, you are operating on a list and x<0 is going to return a list of booleans, not an atom. The vector conditional is ?
Nonetheless, it looks like you want a resulting integer/long anyway, so just use parse here
q)t:(string (10?-1 1)*10?10000f)
q)select "F"$x from t
x
-------------------
4123.1701336801052
-9877.8444156050682
-3867.3530425876379
7267.8099689073861
4046.5459413826466
-8355.0649625249207
6427.3701561614871
-5830.2619284950197
1424.9352994374931
-9149.8820902779698
q)select "j"$"F"$x from t
x
-----
4123
-9878
-3867
7268
4047
-8355
6427
-5830
1425
-9150
answered Nov 22 at 21:52
Sean O'Hagan
1,544311
answered Nov 22 at 21:52
Sean O'Hagan
1,544311
answered Nov 22 at 21:52
Sean O'Hagan
1,544311
answered Nov 22 at 21:52
Sean O'Hagan
1,544311
1,544311
flooris faster than"j"$
– geocar
Nov 26 at 10:09
1
flooris faster than"j"$but"j"$is faster thansignum[x]*floor abs[x]+.5which is the comparable operation
– terrylynch
Nov 26 at 13:59
In other words"j"$has implicit rounding to nearest
– terrylynch
Nov 26 at 14:01
add a comment |
flooris faster than"j"$
– geocar
Nov 26 at 10:09
1
flooris faster than"j"$but"j"$is faster thansignum[x]*floor abs[x]+.5which is the comparable operation
– terrylynch
Nov 26 at 13:59
In other words"j"$has implicit rounding to nearest
– terrylynch
Nov 26 at 14:01
floor is faster than "j"$– geocar
Nov 26 at 10:09
floor is faster than "j"$– geocar
Nov 26 at 10:09
1
1
floor is faster than "j"$ but "j"$ is faster than signum[x]*floor abs[x]+.5 which is the comparable operation– terrylynch
Nov 26 at 13:59
floor is faster than "j"$ but "j"$ is faster than signum[x]*floor abs[x]+.5 which is the comparable operation– terrylynch
Nov 26 at 13:59
In other words
"j"$ has implicit rounding to nearest– terrylynch
Nov 26 at 14:01
In other words
"j"$ has implicit rounding to nearest– terrylynch
Nov 26 at 14:01
add a comment |
To add to what Sean's said, if you wanted to use your function as well you could use each which will apply .q.rnd to each item in the list.
q)select .q.rnd each "F"$x from t
x
-----
-3928
5171
5160
-4067
-1781
3018
-7850
5347
-7112
-4116
but using select "F"$x from t is better as it is vectorised.
q)t:1000 select "j"$"F"$x from t
22
q)t:1000 select .q.rnd each "F"$x from t
33
Also it should be noted that the .q namespace isn't necessary and is "reserved for kx use". A lot of the default q functions are in the .q namespace and there's always a chance future kdb updates could add a .q.rnd that has different behaviour and will break any code where you have used your function in.
answered Nov 23 at 10:22
O.Kaye
561
add a comment |
To add to what Sean's said, if you wanted to use your function as well you could use each which will apply .q.rnd to each item in the list.
q)select .q.rnd each "F"$x from t
x
-----
-3928
5171
5160
-4067
-1781
3018
-7850
5347
-7112
-4116
but using select "F"$x from t is better as it is vectorised.
q)t:1000 select "j"$"F"$x from t
22
q)t:1000 select .q.rnd each "F"$x from t
33
Also it should be noted that the .q namespace isn't necessary and is "reserved for kx use". A lot of the default q functions are in the .q namespace and there's always a chance future kdb updates could add a .q.rnd that has different behaviour and will break any code where you have used your function in.
answered Nov 23 at 10:22
O.Kaye
561
add a comment |
To add to what Sean's said, if you wanted to use your function as well you could use each which will apply .q.rnd to each item in the list.
q)select .q.rnd each "F"$x from t
x
-----
-3928
5171
5160
-4067
-1781
3018
-7850
5347
-7112
-4116
but using select "F"$x from t is better as it is vectorised.
q)t:1000 select "j"$"F"$x from t
22
q)t:1000 select .q.rnd each "F"$x from t
33
Also it should be noted that the .q namespace isn't necessary and is "reserved for kx use". A lot of the default q functions are in the .q namespace and there's always a chance future kdb updates could add a .q.rnd that has different behaviour and will break any code where you have used your function in.
answered Nov 23 at 10:22
O.Kaye
561
To add to what Sean's said, if you wanted to use your function as well you could use each which will apply .q.rnd to each item in the list.
q)select .q.rnd each "F"$x from t
x
-----
-3928
5171
5160
-4067
-1781
3018
-7850
5347
-7112
-4116
but using select "F"$x from t is better as it is vectorised.
q)t:1000 select "j"$"F"$x from t
22
q)t:1000 select .q.rnd each "F"$x from t
33
Also it should be noted that the .q namespace isn't necessary and is "reserved for kx use". A lot of the default q functions are in the .q namespace and there's always a chance future kdb updates could add a .q.rnd that has different behaviour and will break any code where you have used your function in.
answered Nov 23 at 10:22
O.Kaye
561
answered Nov 23 at 10:22
O.Kaye
561
answered Nov 23 at 10:22
O.Kaye
561
answered Nov 23 at 10:22
O.Kaye
561
561
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53438114%2fkdb-select-and-round-off-each-row%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
$is scalar conditional, but?is vector conditional. If you just used that, it would work.– geocar
Nov 26 at 10:08
1
Also:
?[x<0;-1;x>0;1;0]is calledsignum x– geocar
Nov 26 at 10:08
1
Also: Don't name things beginning with
.q– geocar
Nov 26 at 10:10