Merge and match two tables with dates in each

[anufrijv]

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...

1. 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+. 

Regards, 

VA. 

[Sean O'Hagan]

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/joins

example..

//random dates from feb, april, jul, oct
d:raze (5?10)+/:`date$2016.02 2016.04 2016.07 2016.10m
//two random tables
q)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 5
2016.07.01| 0 3 ,2
2016.04.02| 3 8 `long$()
2016.10.06| 1 9 `long$()
2016.02.06| ,3 `long$()
2016.02.03| ,8 5 7
2016.04.01| `long$() ,7
2016.04.03| `long$() ,5
2016.07.06| `long$() ,7
2016.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...@googlegroups.com [personal...@googlegroups.com] on behalf of anufrijv [vadi...@gmail.com]
Sent: 19 October 2016 09:51
To: Kdb+ Personal Developers
Subject: [personal kdb+] Merge and match two tables with dates in each

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbpl...@googlegroups.com<mailto:personal-kdbpl...@googlegroups.com>.
To post to this group, send email to personal...@googlegroups.com<mailto:personal...@googlegroups.com>.
Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.

[anufrijv]

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. 

[Jamie O' Mahony]

Create tab1/tab2:

q)tab1:([]date:2016.02 2016.04 2016.07 2016.10m;data1:2.3 2 2.1 1.9)

q)tab2:([]date:2016.02m+til 12;data1:50.3 51.1 49.8 48 49.9 54.1 49.9 53.1 52.1 49.1 55.8 100)

q)`date`data1`data2 xcols (`date`data2 xcol tab2)lj `date xkey tab1

Explanation:

xcol renames the columns of tab2: http://code.kx.com/wiki/Reference/xcol

(`date`data2 xcol tab2)lj `date xkey tab1 -> <unkeyed table> lj <keyed table>

Take a look at lj here:

http://code.kx.com/wiki/Reference/lj

Then xcols reorders the table columns: http://code.kx.com/wiki/Reference/xcols 

[anufrijv]

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:

[QL Zhuo]

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.

ZHUO QL (KDr2), http://kdr2.com

--

You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.

To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbplus+unsubscribe@googlegroups.com.
To post to this group, send email to personal-kdbplus@googlegroups.com.

[Sean O'Hagan]

books
http://code.kx.com/wiki/QforMortals3http://code.kx.com/wiki/QforMortals3
https://kx.com/2015/03/25/scalable-maintainable-kdb/

online docs
http://code.kx.com/wiki/JB:KdbplusForMortals/contents
http://code.kx.com/wiki/Tutorials
http://code.kx.com/wiki/JB:QforMortals2/contents
http://code.kx.com/wiki/QforMortals3


________________________________
From: personal...@googlegroups.com [personal...@googlegroups.com] on behalf of QL Zhuo [zhuo...@gmail.com]
Sent: 20 October 2016 05:29
To: personal...@googlegroups.com
Subject: Re: [personal kdb+] Re: Merge and match two tables with dates in each

Sorry, but I can't help asking what is "the book"? I'm a newbie too.

--

This email address (zhuo.dev<at>gmail.com<http://gmail.com>) is only for development affairs, e.g. mail list, please mail to zhuo<at>hexoasis.com<http://hexoasis.com> or zhuoql<at>zoho.com<http://zoho.com> for other purpose.

ZHUO QL (KDr2), http://kdr2.com

To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbpl...@googlegroups.com<mailto:personal-kdbpl...@googlegroups.com>.
To post to this group, send email to personal...@googlegroups.com<mailto:personal...@googlegroups.com>.

Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.

To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbpl...@googlegroups.com<mailto:personal-kdbpl...@googlegroups.com>.
To post to this group, send email to personal...@googlegroups.com<mailto:personal...@googlegroups.com>.

[QL Zhuo]

Thanks!

--

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.

ZHUO QL (KDr2), http://kdr2.com

On Thu, Oct 20, 2016 at 7:07 PM, Sean O'Hagan <soh...@firstderivatives.com> wrote:

books
http://code.kx.com/wiki/QforMortals3http://code.kx.com/wiki/QforMortals3
https://kx.com/2015/03/25/scalable-maintainable-kdb/

online docs
http://code.kx.com/wiki/JB:KdbplusForMortals/contents
http://code.kx.com/wiki/Tutorials
http://code.kx.com/wiki/JB:QforMortals2/contents
http://code.kx.com/wiki/QforMortals3


________________________________

From: personal-kdbplus@googlegroups.com [personal-kdbplus@googlegroups.com] on behalf of QL Zhuo [zhuo...@gmail.com]

Sent: 20 October 2016 05:29

To: personal-kdbplus@googlegroups.com

Subject: Re: [personal kdb+] Re: Merge and match two tables with dates in each

Sorry, but I can't help asking what is "the book"? I'm a newbie too.

--
This email address (zhuo.dev<at>gmail.com<http://gmail.com>) is only for development affairs, e.g. mail list, please mail to zhuo<at>hexoasis.com<http://hexoasis.com> or zhuoql<at>zoho.com<http://zoho.com> for other purpose.

ZHUO QL (KDr2), http://kdr2.com

To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbplus+unsubscribe@googlegroups.com<mailto:personal-kdbplus+unsubscribe@googlegroups.com>.
To post to this group, send email to personal-kdbplus@googlegroups.com<mailto:personal-kdbplus@googlegroups.com>.

Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.

To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbplus+unsubscribe@googlegroups.com<mailto:personal-kdbplus+unsubscribe@googlegroups.com>.
To post to this group, send email to personal-kdbplus@googlegroups.com<mailto:personal-kdbplus@googlegroups.com>.

Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.

To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbplus+unsubscribe@googlegroups.com.
To post to this group, send email to personal-kdbplus@googlegroups.com.