Joining tables

Community Support
1

Hi,

 

I have a minor problem and I hope youcould help me. I’d like to combine two tables but I don’t know what kind ofjoin I should do. In table one I have 3 different rows for same Period, Pos andRank and I should be able to join it with Table two which includes one thousandrows for same Period,Pos and Rank. The result should then include 3000 thousandrows for same Period.Pos and Rank.

 

Best regards,

 

Kimmo Linna

 

Table one

 

Period  Pos    RankTraffic Share       DutyE

2017.10 520525 1   LH      0.008216541 26.7

2017.10 520525 1   Other   0.1678539   8.9

2017.10 520525 1   SH      0.8239296   8.9

2017.10 520526 0   LH      0.07195026  10.8

2017.10 520526 0   Other   0.1872651   3.6

2017.10 520526 0   SH      0.7407846   3.6

…

 

Table two

 

Period  Pos    RankDuty

2017.10 520525 1    115.9

2017.10 520525 1    59.4

2017.10 520525 1    85.4

2017.10 520525 1    82.8

2017.10 520525 1    95.1

2017.10 520525 1    101.3

…

 

Result

 

Period      Pos    Rank Duty  TrafficShare       DutyE

2017.10 520525 1    115.9LH      0.008216541 26.7

2017.10 520525 1    115.9Other   0.1678539   8.9

2017.10 520525 1    115.9SH      0.8239296   8.9

2017.10 520525 1   59.4  LH      0.008216541 26.7

2017.10 520525 1   59.4  Other   0.1678539   8.9

2017.10 520525 1   59.4  SH      0.8239296   8.9

2

have you considered ‘ej’: https://code.kx.com/v2/ref/ej

show t1:ungroup (Period:2017.10m;Pos:520525+0 1;Rank:1 0;Traffic:2#enlistLHOther`LS;Share:3?/:2#1f;DutyE:3?/:2#100f)
Period  Pos    Rank Traffic Share     DutyE  

2017.10 520525 1    LH      0.2727631 76.54862
2017.10 520525 1    Other   0.7844963 27.81124
2017.10 520525 1    LS      0.7747729 75.80135
2017.10 520526 0    LH      0.7303534 6.051775
2017.10 520526 0    Other   0.623454  65.68238
2017.10 520526 0    LS      0.4985733 81.21747
q)show t2:(Period:2017.10m;Pos:520525;Rank:1;Duty:1000?200f)
Period  Pos    Rank Duty    

2017.10 520525 1    165.2182
2017.10 520525 1    128.9343
2017.10 520525 1    43.73812
2017.10 520525 1    15.20158
2017.10 520525 1    183.2601
..
q)ej[`Period`Pos`Rank;t2;t1]
Period  Pos    Rank Duty     Traffic Share     DutyE  

2017.10 520525 1    165.2182 LH      0.2727631 76.54862
2017.10 520525 1    165.2182 Other   0.7844963 27.81124
2017.10 520525 1    165.2182 LS      0.7747729 75.80135
2017.10 520525 1    128.9343 LH      0.2727631 76.54862
2017.10 520525 1    128.9343 Other   0.7844963 27.81124
..

3

Hi Nick,

Thank you very much. I knew It wasn’t so compicated solution, but I couldn’t figure it out by myself.

Best regards,

Kimmo 

torstai 31. lokakuuta 2019 3.24.00 UTC+2 Nick kirjoitti:

have you considered ‘ej’: https://code.kx.com/v2/ref/ej

show t1:ungroup (Period:2017.10m;Pos:520525+0 1;Rank:1 0;Traffic:2#enlistLHOther<wbr>LS;Share:3?/:2#1f;DutyE:3?/:2#<wbr>100f)<br>Period Pos Rank Traffic Share DutyE <br>------------------------------<wbr>----------------<br>2017.10 520525 1 LH 0.2727631 76.54862<br>2017.10 520525 1 Other 0.7844963 27.81124<br>2017.10 520525 1 LS 0.7747729 75.80135<br>2017.10 520526 0 LH 0.7303534 6.051775<br>2017.10 520526 0 Other 0.623454 65.68238<br>2017.10 520526 0 LS 0.4985733 81.21747<br>q)show t2:([]Period:2017.10m;Pos:<wbr>520525;Rank:1;Duty:1000?200f)<br>Period Pos Rank Duty <br>----------------------------<br>2017.10 520525 1 165.2182<br>2017.10 520525 1 128.9343<br>2017.10 520525 1 43.73812<br>2017.10 520525 1 15.20158<br>2017.10 520525 1 183.2601<br>..<br>q)ej[PeriodPosRank;t2;t1]
Period Pos Rank Duty Traffic Share DutyE
-------------------------------------------------------
2017.10 520525 1 165.2182 LH 0.2727631 76.54862
2017.10 520525 1 165.2182 Other 0.7844963 27.81124
2017.10 520525 1 165.2182 LS 0.7747729 75.80135
2017.10 520525 1 128.9343 LH 0.2727631 76.54862
2017.10 520525 1 128.9343 Other 0.7844963 27.81124
..