Join Many-to-One, Keep Calculation

[JustinDallas]

Hello Everyone,

I have to join to tables, where one table relates to many rows of the other table.  My only issue is that if I do so, my counts and sums will go through the roof for the "one" rows since it will be duplicated.  For example, lets say my script looks like this:

Athletes:
LOAD * Inline
[
  %playerId, Name, Location, Salary
    1,'Lebron James', Cleveland, '10000000'
    2,'Babe Ruth',New York, '9000000'
    3,'Cam Newton',Charlotte,'8000000'
]
;


LEFT JOIN(Athletes)
LOAD * Inline
[
  %playerId, GameName, Points
    1,'BB1',10
    1,'BB2',20
    1,'BB3',30
    2,'Home Run',4
    2,'Strikeout',2
    2,'Foul Ball',1
    3,'Touchdown',7
    3,'Pick Six',-1
    3,'Hail Mary',5
]
;

How would I have a table so that the Sum of the salaries for the players so that they aren't multiplied by the number of Games theyve' played?

Any help is greatly appreciated.

[Anonymous]

Don't do a LEFT JOIN do a LEFT KEEP.

Keep the data in 2 separate tables.

Then I believe you can have your measures simply be sum(Salary) or sum(Points) and they'll work fine.

1. Athletes: 
2. LOAD * Inline 
3. [ 
4.   %playerId, Name, Location, Salary 
5.     1,'Lebron James', Cleveland, '10000000' 
6.     2,'Babe Ruth',New York, '9000000' 
7.     3,'Cam Newton',Charlotte,'8000000' 
8. ] 
9. ; 
10.  
11.  
12. LEFT KEEP(Athletes) 
13. LOAD * Inline 
14. [ 
15.   %playerId, GameName, Points 
16.     1,'BB1',10 
17.     1,'BB2',20 
18.     1,'BB3',30 
19.     2,'Home Run',4 
20.     2,'Strikeout',2 
21.     2,'Foul Ball',1 
22.     3,'Touchdown',7 
23.     3,'Pick Six',-1 
24.     3,'Hail Mary',5 
25. ] 
26. ; 

[Clever_Anjos · Accepted solution]

Maybe this can return what you need

Sum(Aggr(Only(Salary),%playerId))

[JustinDallas]

I ended up discovering the answer literally the second after I hit "Submit" on the question.

[JustinDallas]

My real problem is more complex than the example, and it requires a Join otherwise the datamodel becomes EXTREMELY obnoxious.