4 Replies Latest reply: Jun 14, 2017 11:38 AM by Justin Dallas

# Join Many-to-One, Keep Calculation

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.

• ###### Re: Join Many-to-One, Keep Calculation

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.   %playerId, Name, Location, Salary
4.     1,'Lebron James', Cleveland, '10000000'
5.     2,'Babe Ruth',New York, '9000000'
6.     3,'Cam Newton',Charlotte,'8000000'
7.
8.
9. LEFT KEEP(Athletes)
10. LOAD * Inline
11.   %playerId, GameName, Points
12.     1,'BB1',10
13.     1,'BB2',20
14.     1,'BB3',30
15.     2,'Home Run',4
16.     2,'Strikeout',2
17.     2,'Foul Ball',1
18.     3,'Touchdown',7
19.     3,'Pick Six',-1
20.     3,'Hail Mary',5
• ###### Re: Join Many-to-One, Keep Calculation

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

• ###### Re: Join Many-to-One, Keep Calculation

Maybe this can return what you need

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

• ###### Re: Join Many-to-One, Keep Calculation

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