cancel
Showing results for 
Search instead for 
Did you mean: 

Joining tables

Kimmo_Linna
New Contributor II

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 REPLIES 2

Nick
New Contributor
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#enlist`LH`Other`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
..

Kimmo_Linna
New Contributor II
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#enlist`LH`Other`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
..