KDB - Filter List Column Based on Another Column
I'm struggling with eliminating data from my query. I have attached a picture with my data results (data itself is too large and has customer info so I can't include). I have two tables that I'm joining by SKU to show when we enter a SKU into the system and when we sell it. We reuse SKUs based on vendors which isn't the best practice but is currently a necessity. What I'd like to do is eliminate the InvoiceDates where InvoiceDate < TransferDate. So in the InvoiceDate column it would only show the highlighted yellow dates for the first few rows.
Please let me know if you have any questions and thanks for the help!
kdb
edited Nov 14 at 7:49
user2242865
497312
asked Nov 11 at 17:18
ks-man
778
add a comment |
I'm struggling with eliminating data from my query. I have attached a picture with my data results (data itself is too large and has customer info so I can't include). I have two tables that I'm joining by SKU to show when we enter a SKU into the system and when we sell it. We reuse SKUs based on vendors which isn't the best practice but is currently a necessity. What I'd like to do is eliminate the InvoiceDates where InvoiceDate < TransferDate. So in the InvoiceDate column it would only show the highlighted yellow dates for the first few rows.
Please let me know if you have any questions and thanks for the help!
kdb
edited Nov 14 at 7:49
user2242865
497312
asked Nov 11 at 17:18
ks-man
778
What do you mean by elimindate invoice dates where invoiceDate? Do you want to delete the row if a particular invoceDate is present in invoiceDate column of that row?
– Rahul
Nov 11 at 18:34
Sorry, my formatting was bad so it ignored the part. I fixed the question.
– ks-man
Nov 11 at 21:38
add a comment |
I'm struggling with eliminating data from my query. I have attached a picture with my data results (data itself is too large and has customer info so I can't include). I have two tables that I'm joining by SKU to show when we enter a SKU into the system and when we sell it. We reuse SKUs based on vendors which isn't the best practice but is currently a necessity. What I'd like to do is eliminate the InvoiceDates where InvoiceDate < TransferDate. So in the InvoiceDate column it would only show the highlighted yellow dates for the first few rows.
Please let me know if you have any questions and thanks for the help!
kdb
edited Nov 14 at 7:49
user2242865
497312
asked Nov 11 at 17:18
ks-man
778
I'm struggling with eliminating data from my query. I have attached a picture with my data results (data itself is too large and has customer info so I can't include). I have two tables that I'm joining by SKU to show when we enter a SKU into the system and when we sell it. We reuse SKUs based on vendors which isn't the best practice but is currently a necessity. What I'd like to do is eliminate the InvoiceDates where InvoiceDate < TransferDate. So in the InvoiceDate column it would only show the highlighted yellow dates for the first few rows.
Please let me know if you have any questions and thanks for the help!
kdb
kdb
edited Nov 14 at 7:49
user2242865
497312
asked Nov 11 at 17:18
ks-man
778
edited Nov 14 at 7:49
user2242865
497312
asked Nov 11 at 17:18
ks-man
778
edited Nov 14 at 7:49
user2242865
497312
edited Nov 14 at 7:49
user2242865
497312
edited Nov 14 at 7:49
user2242865
497312
497312
asked Nov 11 at 17:18
ks-man
778
asked Nov 11 at 17:18
ks-man
778
asked Nov 11 at 17:18
ks-man
778
778
What do you mean by elimindate invoice dates where invoiceDate? Do you want to delete the row if a particular invoceDate is present in invoiceDate column of that row?
– Rahul
Nov 11 at 18:34
Sorry, my formatting was bad so it ignored the part. I fixed the question.
– ks-man
Nov 11 at 21:38
add a comment |
What do you mean by elimindate invoice dates where invoiceDate? Do you want to delete the row if a particular invoceDate is present in invoiceDate column of that row?
– Rahul
Nov 11 at 18:34
Sorry, my formatting was bad so it ignored the part. I fixed the question.
– ks-man
Nov 11 at 21:38
What do you mean by elimindate invoice dates where invoiceDate? Do you want to delete the row if a particular invoceDate is present in invoiceDate column of that row?
– Rahul
Nov 11 at 18:34
What do you mean by elimindate invoice dates where invoiceDate? Do you want to delete the row if a particular invoceDate is present in invoiceDate column of that row?
– Rahul
Nov 11 at 18:34
Sorry, my formatting was bad so it ignored the part. I fixed the question.
– ks-man
Nov 11 at 21:38
Sorry, my formatting was bad so it ignored the part. I fixed the question.
– ks-man
Nov 11 at 21:38
add a comment |
2 Answers
2
active
oldest
votes
This would work:
q) update InvoiceDate:{x where x >= y}'[InvoiceDate;TransferDate] from tbl
Explanation:
Above query uses 'each-both(') function to iterate over InvoiceDate and TransferDate values pair wise(indirectly row wise), pass each pair to lambda function as 'x' and 'y' and then select 'x'(InvoiceDate) which are >= 'y'(TransferDate)
answered Nov 11 at 21:53
Rahul
2,162723
Thanks! This works great. Definitely going to need to learn more about this each-both function as I feel it could help me a lot. I found this paper, do you know of other resources for teaching myself more about this and similar concepts? Thanks again! code.kx.com/q/wp/efficient_use_of_adverbs.pdf
– ks-man
Nov 12 at 12:37
Adverbs are higher order functions which are very useful. You can read details about them on KX wiki: code.kx.com/q/ref/adverbs
– Rahul
Nov 12 at 13:04
One issue I'm having is that I can't export the result to a .csv. The issue seems to be with the InvoiceDate column. If I delete that column the export works fine however with the column included I get a Type error on the save command. I can export the selection from QPad to a csv without issue. Do you know why I can't save it down through KDB? I'm using the save command$"C:/q/tbl.csv"Thanks!
– ks-man
Nov 16 at 4:03
Thats because 'save' does not support nested structure and invoceDate is nested type. For that you have to convert values of invoiceDate to string and then save it. Ex: t:update invoiceDate:" " sv' string invoiceDate from t; save `t.csv. Also when you read this table back, you will have to cast invoiceDate back to 'date' type.
– Rahul
Nov 16 at 10:30
That did it. Thanks again!
– ks-man
Nov 16 at 14:15
add a comment |
You question is cut off, but I'm guessing you want to filter on whether a particular date is in your invoiceDate lists. You can do this as follows:
q)select from tbl where in[2019.01.01;] each invoiceDate
If this isn't what you are looking for, please clarify above with an example
answered Nov 11 at 20:28
user2242865
497312
Sorry, the formatting was bad so it cut off the question. I fixed it above but my question is to eliminate only the dates in the InvoiceDate column that are after the TransferDate. So it would only show the highlighted yellow data.
– ks-man
Nov 11 at 21:39
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%2f53251216%2fkdb-filter-list-column-based-on-another-column%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
This would work:
q) update InvoiceDate:{x where x >= y}'[InvoiceDate;TransferDate] from tbl
Explanation:
Above query uses 'each-both(') function to iterate over InvoiceDate and TransferDate values pair wise(indirectly row wise), pass each pair to lambda function as 'x' and 'y' and then select 'x'(InvoiceDate) which are >= 'y'(TransferDate)
answered Nov 11 at 21:53
Rahul
2,162723
Thanks! This works great. Definitely going to need to learn more about this each-both function as I feel it could help me a lot. I found this paper, do you know of other resources for teaching myself more about this and similar concepts? Thanks again! code.kx.com/q/wp/efficient_use_of_adverbs.pdf
– ks-man
Nov 12 at 12:37
Adverbs are higher order functions which are very useful. You can read details about them on KX wiki: code.kx.com/q/ref/adverbs
– Rahul
Nov 12 at 13:04
One issue I'm having is that I can't export the result to a .csv. The issue seems to be with the InvoiceDate column. If I delete that column the export works fine however with the column included I get a Type error on the save command. I can export the selection from QPad to a csv without issue. Do you know why I can't save it down through KDB? I'm using the save command$"C:/q/tbl.csv"Thanks!
– ks-man
Nov 16 at 4:03
Thats because 'save' does not support nested structure and invoceDate is nested type. For that you have to convert values of invoiceDate to string and then save it. Ex: t:update invoiceDate:" " sv' string invoiceDate from t; save `t.csv. Also when you read this table back, you will have to cast invoiceDate back to 'date' type.
– Rahul
Nov 16 at 10:30
That did it. Thanks again!
– ks-man
Nov 16 at 14:15
add a comment |
This would work:
q) update InvoiceDate:{x where x >= y}'[InvoiceDate;TransferDate] from tbl
Explanation:
Above query uses 'each-both(') function to iterate over InvoiceDate and TransferDate values pair wise(indirectly row wise), pass each pair to lambda function as 'x' and 'y' and then select 'x'(InvoiceDate) which are >= 'y'(TransferDate)
answered Nov 11 at 21:53
Rahul
2,162723
Thanks! This works great. Definitely going to need to learn more about this each-both function as I feel it could help me a lot. I found this paper, do you know of other resources for teaching myself more about this and similar concepts? Thanks again! code.kx.com/q/wp/efficient_use_of_adverbs.pdf
– ks-man
Nov 12 at 12:37
Adverbs are higher order functions which are very useful. You can read details about them on KX wiki: code.kx.com/q/ref/adverbs
– Rahul
Nov 12 at 13:04
One issue I'm having is that I can't export the result to a .csv. The issue seems to be with the InvoiceDate column. If I delete that column the export works fine however with the column included I get a Type error on the save command. I can export the selection from QPad to a csv without issue. Do you know why I can't save it down through KDB? I'm using the save command$"C:/q/tbl.csv"Thanks!
– ks-man
Nov 16 at 4:03
Thats because 'save' does not support nested structure and invoceDate is nested type. For that you have to convert values of invoiceDate to string and then save it. Ex: t:update invoiceDate:" " sv' string invoiceDate from t; save `t.csv. Also when you read this table back, you will have to cast invoiceDate back to 'date' type.
– Rahul
Nov 16 at 10:30
That did it. Thanks again!
– ks-man
Nov 16 at 14:15
add a comment |
This would work:
q) update InvoiceDate:{x where x >= y}'[InvoiceDate;TransferDate] from tbl
Explanation:
Above query uses 'each-both(') function to iterate over InvoiceDate and TransferDate values pair wise(indirectly row wise), pass each pair to lambda function as 'x' and 'y' and then select 'x'(InvoiceDate) which are >= 'y'(TransferDate)
answered Nov 11 at 21:53
Rahul
2,162723
This would work:
q) update InvoiceDate:{x where x >= y}'[InvoiceDate;TransferDate] from tbl
Explanation:
Above query uses 'each-both(') function to iterate over InvoiceDate and TransferDate values pair wise(indirectly row wise), pass each pair to lambda function as 'x' and 'y' and then select 'x'(InvoiceDate) which are >= 'y'(TransferDate)
answered Nov 11 at 21:53
Rahul
2,162723
edited Nov 11 at 22:00
answered Nov 11 at 21:53
Rahul
2,162723
answered Nov 11 at 21:53
Rahul
2,162723
answered Nov 11 at 21:53
Rahul
2,162723
2,162723
Thanks! This works great. Definitely going to need to learn more about this each-both function as I feel it could help me a lot. I found this paper, do you know of other resources for teaching myself more about this and similar concepts? Thanks again! code.kx.com/q/wp/efficient_use_of_adverbs.pdf
– ks-man
Nov 12 at 12:37
Adverbs are higher order functions which are very useful. You can read details about them on KX wiki: code.kx.com/q/ref/adverbs
– Rahul
Nov 12 at 13:04
One issue I'm having is that I can't export the result to a .csv. The issue seems to be with the InvoiceDate column. If I delete that column the export works fine however with the column included I get a Type error on the save command. I can export the selection from QPad to a csv without issue. Do you know why I can't save it down through KDB? I'm using the save command$"C:/q/tbl.csv"Thanks!
– ks-man
Nov 16 at 4:03
Thats because 'save' does not support nested structure and invoceDate is nested type. For that you have to convert values of invoiceDate to string and then save it. Ex: t:update invoiceDate:" " sv' string invoiceDate from t; save `t.csv. Also when you read this table back, you will have to cast invoiceDate back to 'date' type.
– Rahul
Nov 16 at 10:30
That did it. Thanks again!
– ks-man
Nov 16 at 14:15
add a comment |
Thanks! This works great. Definitely going to need to learn more about this each-both function as I feel it could help me a lot. I found this paper, do you know of other resources for teaching myself more about this and similar concepts? Thanks again! code.kx.com/q/wp/efficient_use_of_adverbs.pdf
– ks-man
Nov 12 at 12:37
Adverbs are higher order functions which are very useful. You can read details about them on KX wiki: code.kx.com/q/ref/adverbs
– Rahul
Nov 12 at 13:04
One issue I'm having is that I can't export the result to a .csv. The issue seems to be with the InvoiceDate column. If I delete that column the export works fine however with the column included I get a Type error on the save command. I can export the selection from QPad to a csv without issue. Do you know why I can't save it down through KDB? I'm using the save command$"C:/q/tbl.csv"Thanks!
– ks-man
Nov 16 at 4:03
Thats because 'save' does not support nested structure and invoceDate is nested type. For that you have to convert values of invoiceDate to string and then save it. Ex: t:update invoiceDate:" " sv' string invoiceDate from t; save `t.csv. Also when you read this table back, you will have to cast invoiceDate back to 'date' type.
– Rahul
Nov 16 at 10:30
That did it. Thanks again!
– ks-man
Nov 16 at 14:15
Thanks! This works great. Definitely going to need to learn more about this each-both function as I feel it could help me a lot. I found this paper, do you know of other resources for teaching myself more about this and similar concepts? Thanks again! code.kx.com/q/wp/efficient_use_of_adverbs.pdf
– ks-man
Nov 12 at 12:37
Thanks! This works great. Definitely going to need to learn more about this each-both function as I feel it could help me a lot. I found this paper, do you know of other resources for teaching myself more about this and similar concepts? Thanks again! code.kx.com/q/wp/efficient_use_of_adverbs.pdf
– ks-man
Nov 12 at 12:37
Adverbs are higher order functions which are very useful. You can read details about them on KX wiki: code.kx.com/q/ref/adverbs
– Rahul
Nov 12 at 13:04
Adverbs are higher order functions which are very useful. You can read details about them on KX wiki: code.kx.com/q/ref/adverbs
– Rahul
Nov 12 at 13:04
One issue I'm having is that I can't export the result to a .csv. The issue seems to be with the InvoiceDate column. If I delete that column the export works fine however with the column included I get a Type error on the save command. I can export the selection from QPad to a csv without issue. Do you know why I can't save it down through KDB? I'm using the save command
$"C:/q/tbl.csv" Thanks!– ks-man
Nov 16 at 4:03
One issue I'm having is that I can't export the result to a .csv. The issue seems to be with the InvoiceDate column. If I delete that column the export works fine however with the column included I get a Type error on the save command. I can export the selection from QPad to a csv without issue. Do you know why I can't save it down through KDB? I'm using the save command
$"C:/q/tbl.csv" Thanks!– ks-man
Nov 16 at 4:03
Thats because 'save' does not support nested structure and invoceDate is nested type. For that you have to convert values of invoiceDate to string and then save it. Ex: t:update invoiceDate:" " sv' string invoiceDate from t; save `t.csv. Also when you read this table back, you will have to cast invoiceDate back to 'date' type.
– Rahul
Nov 16 at 10:30
Thats because 'save' does not support nested structure and invoceDate is nested type. For that you have to convert values of invoiceDate to string and then save it. Ex: t:update invoiceDate:" " sv' string invoiceDate from t; save `t.csv. Also when you read this table back, you will have to cast invoiceDate back to 'date' type.
– Rahul
Nov 16 at 10:30
That did it. Thanks again!
– ks-man
Nov 16 at 14:15
That did it. Thanks again!
– ks-man
Nov 16 at 14:15
add a comment |
You question is cut off, but I'm guessing you want to filter on whether a particular date is in your invoiceDate lists. You can do this as follows:
q)select from tbl where in[2019.01.01;] each invoiceDate
If this isn't what you are looking for, please clarify above with an example
answered Nov 11 at 20:28
user2242865
497312
Sorry, the formatting was bad so it cut off the question. I fixed it above but my question is to eliminate only the dates in the InvoiceDate column that are after the TransferDate. So it would only show the highlighted yellow data.
– ks-man
Nov 11 at 21:39
add a comment |
You question is cut off, but I'm guessing you want to filter on whether a particular date is in your invoiceDate lists. You can do this as follows:
q)select from tbl where in[2019.01.01;] each invoiceDate
If this isn't what you are looking for, please clarify above with an example
answered Nov 11 at 20:28
user2242865
497312
Sorry, the formatting was bad so it cut off the question. I fixed it above but my question is to eliminate only the dates in the InvoiceDate column that are after the TransferDate. So it would only show the highlighted yellow data.
– ks-man
Nov 11 at 21:39
add a comment |
You question is cut off, but I'm guessing you want to filter on whether a particular date is in your invoiceDate lists. You can do this as follows:
q)select from tbl where in[2019.01.01;] each invoiceDate
If this isn't what you are looking for, please clarify above with an example
answered Nov 11 at 20:28
user2242865
497312
You question is cut off, but I'm guessing you want to filter on whether a particular date is in your invoiceDate lists. You can do this as follows:
q)select from tbl where in[2019.01.01;] each invoiceDate
If this isn't what you are looking for, please clarify above with an example
answered Nov 11 at 20:28
user2242865
497312
answered Nov 11 at 20:28
user2242865
497312
answered Nov 11 at 20:28
user2242865
497312
answered Nov 11 at 20:28
user2242865
497312
497312
Sorry, the formatting was bad so it cut off the question. I fixed it above but my question is to eliminate only the dates in the InvoiceDate column that are after the TransferDate. So it would only show the highlighted yellow data.
– ks-man
Nov 11 at 21:39
add a comment |
Sorry, the formatting was bad so it cut off the question. I fixed it above but my question is to eliminate only the dates in the InvoiceDate column that are after the TransferDate. So it would only show the highlighted yellow data.
– ks-man
Nov 11 at 21:39
Sorry, the formatting was bad so it cut off the question. I fixed it above but my question is to eliminate only the dates in the InvoiceDate column that are after the TransferDate. So it would only show the highlighted yellow data.
– ks-man
Nov 11 at 21:39
Sorry, the formatting was bad so it cut off the question. I fixed it above but my question is to eliminate only the dates in the InvoiceDate column that are after the TransferDate. So it would only show the highlighted yellow data.
– ks-man
Nov 11 at 21:39
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%2f53251216%2fkdb-filter-list-column-based-on-another-column%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
What do you mean by elimindate invoice dates where invoiceDate? Do you want to delete the row if a particular invoceDate is present in invoiceDate column of that row?
– Rahul
Nov 11 at 18:34
Sorry, my formatting was bad so it ignored the part. I fixed the question.
– ks-man
Nov 11 at 21:38