Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Maybe this can return what you need
Sum(Aggr(Only(Salary),%playerId))
I ended up discovering the answer literally the second after I hit "Submit" on the question.
My real problem is more complex than the example, and it requires a Join otherwise the datamodel becomes EXTREMELY obnoxious.