Hello everyone, newbie here. Got the book, a lot of read…
What would be the best way to merge two tables with dates?
Table1 Table2
Dates Data1 Dates Data1
Feb 1 datapoint1 Feb 1 datapoint2
April 1 datapoint1 Mar 1 datapoint2
Jul 1 datapoint1 Apr 1 datapoint2
Oct 1 datapoint1 May 1 datapoint2
date 5 datapoint1 date 1 datapoint2
date 6 datapoint1 date 1 datapoint2
date 7 datapoint1 date 1 datapoint2
I have two table, each table has dates and some data. Table1 data is released quarterly, mid season: except February. Hence: Feb, April, July, Oct. Table2 data is released monthly, every month. I need to merge these two tables so that the dates match as well. Hence, Feb1 datapoint1 must be on the same row as datapoint2, both released on Feb 1 and so on…
The only way I can think off right now is to merge both tables as such:
a1:Table1, Table2
dates values
1 10
2 20
3 30
4 40
5 50
1 -1
2 -2
5 -3
4 -4
2 -5
2.Then do this:
a2: select Data by Dates
dates| values
-----| --------
1 | 10 -1
2 | 20 -2 -5
3 | ,30
4 | 40 -4
5 | 50 -3
But, I do not see how I can work with the above result. There is literally nothing I can do with that table where a date has multiple data points (under values column)
Please help, perhaps I am overcomplicating the process due to my temporary unfamiliarity with KDB+.
Hi anufrijv ,Welcome to the community.>datapoint1 must be on the same row as datapoint2,Sounds like you want something like a union join, i.e. uj?Have a read here - http://code.kx.com/wiki/Reference/joinsexample..//random dates from feb, april, jul, octd:raze (5?10)+/:date$2016.02 2016.04 2016.07 2016.10m//two random tablesq)t:([]date:10?d;datapoint1:10?10)q)t2:([]date:10?d;datapoint2:10?10)q)uj[date xgroup t;date xgroup t2]date | datapoint1 datapoint2----------| ---------------------2016.10.02| 2 6 3 1 52016.07.01| 0 3 ,22016.04.02| 3 8 long$()2016.10.06| 1 9 long$()2016.02.06| ,3 long$()2016.02.03| ,8 5 72016.04.01| long$() ,72016.04.03| long$() ,52016.07.06| long$() ,72016.10.01| long$() ,2>There is literally nothing I can do with that table where a date has multiple data points (under values column)There always is a way :), but for this example I am not sure why you would need to do something with the values. You want to join two datapoints from two tables based on a date key from what I am reading. Correct me if I am wrong.If you have further questions, a sample table[s] and a sample result might be a clearer way of defining your requirements,HTH,Sean________________________________From: personal-kdbplus@googlegroups.com [personal-kdbplus@googlegroups.com] on behalf of anufrijv [vadim.anu@gmail.com]Sent: 19 October 2016 09:51To: Kdb+ Personal DevelopersSubject: [personal kdb+] Merge and match two tables with dates in eachHello everyone, newbie here. Got the book, a lot of read…What would be the best way to merge two tables with dates? Table1 Table2Dates Data1 Dates Data1Feb 1 datapoint1 Feb 1 datapoint2April 1 datapoint1 Mar 1 datapoint2Jul 1 datapoint1 Apr 1 datapoint2Oct 1 datapoint1 May 1 datapoint2date 5 datapoint1 date 1 datapoint2date 6 datapoint1 date 1 datapoint2date 7 datapoint1 date 1 datapoint2I have two table, each table has dates and some data. Table1 data is released quarterly, mid season: except February. Hence: Feb, April, July, Oct. Table2 data is released monthly, every month. I need to merge these two tables so that the dates match as well. Hence, Feb1 datapoint1 must be on the same row as datapoint2, both released on Feb 1 and so on…1. The only way I can think off right now is to merge both tables as such:a1:Table1, Table2dates values------------1 102 203 304 405 501 -12 -25 -34 -42 -52.Then do this:a2: select Data by Datesdates| values-----| --------1 | 10 -12 | 20 -2 -53 | ,304 | 40 -45 | 50 -3But, I do not see how I can work with the above result. There is literally nothing I can do with that table where a date has multiple data points (under values column)Please help, perhaps I am overcomplicating the process due to my temporary unfamiliarity with KDB+.Regards,VA.-- Submitted via Google Groups
Thank you so much for such a quick reply. What I am trying to do is to run some very simple analysis. The quarterly data is GDP, monthly data is ISM (Institute of Management Supply). I want to merge the tables, and then run simple correlation (autocorrelation) between those two. So I am trying to arrive to a table which will look like this:
Dates Data1 Data2
Feb1 2.3 50.3
Mar1 - 51.1
Apr1 2 49.8
May1 - 48
Jun1 - 49.9
Jul1 2.1 54.1
Aug1 - 49.9
Sep1 - 53.1
Oct 1.9 52.1
Nov1 - 49.1
Dec - 55.8
Jan - 100
As soon as I join the tables, I will need to get the data ready for analysis by taking deltas etc etc, eventually, I will need to run correlation analysis. I am not worried about anything else right now other than just joining the tables.
Sorry, but I can’t help asking what is “the book”? I’m a newbie too.
–
This email address (zhuo.dev<at>gmail.com) is only for development affairs, e.g. mail list, please mail to zhuo<at>hexoasis.com or zhuoql<at>zoho.com for other purpose.
bookshttp://code.kx.com/wiki/QforMortals3http://code.kx.com/wiki/QforMortals3https://kx.com/2015/03/25/scalable-maintainable-kdb/online docshttp://code.kx.com/wiki/JB:KdbplusForMortals/contentshttp://code.kx.com/wiki/Tutorialshttp://code.kx.com/wiki/JB:QforMortals2/contentshttp://code.kx.com/wiki/QforMortals3________________________________From: personal-kdbplus@googlegroups.com [personal-kdbplus@googlegroups.com] on behalf of QL Zhuo [zhuo.dev@gmail.com]Sent: 20 October 2016 05:29To: personal-kdbplus@googlegroups.comSubject: Re: [personal kdb+] Re: Merge and match two tables with dates in eachSorry, but I can’t help asking what is “the book”? I’m a newbie too.–This email address (zhuo.devgmail.comhttp:) is only for development affairs, e.g. mail list, please mail to zhuohexoasis.comhttp: or zhuoqlzoho.comhttp: for other purpose.ZHUO QL (KDr2), http://kdr2.comOn Wed, Oct 19, 2016 at 10:19 PM, anufrijv <vadim.anu>> wrote:This is so great, thank you very much. This is page 283 in the book, I am only on page 100 :) Will get there soon. Thank you once again!On Wednesday, October 19, 2016 at 10:35:26 AM UTC+1, anufrijv wrote:Hello everyone, newbie here. Got the book, a lot of read…What would be the best way to merge two tables with dates? Table1 Table2Dates Data1 Dates Data1Feb 1 datapoint1 Feb 1 datapoint2April 1 datapoint1 Mar 1 datapoint2Jul 1 datapoint1 Apr 1 datapoint2Oct 1 datapoint1 May 1 datapoint2date 5 datapoint1 date 1 datapoint2date 6 datapoint1 date 1 datapoint2date 7 datapoint1 date 1 datapoint2I have two table, each table has dates and some data. Table1 data is released quarterly, mid season: except February. Hence: Feb, April, July, Oct. Table2 data is released monthly, every month. I need to merge these two tables so that the dates match as well. Hence, Feb1 datapoint1 must be on the same row as datapoint2, both released on Feb 1 and so on…1. The only way I can think off right now is to merge both tables as such:a1:Table1, Table2dates values------------1 102 203 304 405 501 -12 -25 -34 -42 -52.Then do this:a2: select Data by Datesdates| values-----| --------1 | 10 -12 | 20 -2 -53 | ,304 | 40 -45 | 50 -3But, I do not see how I can work with the above result. There is literally nothing I can do with that table where a date has multiple data points (under values column)Please help, perhaps I am overcomplicating the process due to my temporary unfamiliarity with KDB+.Regards,VA.–
Submitted via Google Groups</vadim.anu></http:></http:></http:>
–
This email address (zhuo.dev<at>gmail.com) is only for development affairs, e.g. mail list, please mail to zhuo<at>hexoasis.com or zhuoql<at>zoho.com for other purpose.