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

    Join Many-to-One, Keep Calculation

    Justin Dallas

      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?

       

      2017-06-14 10_11_00-Qlik Sense Desktop.png

       

      Any help is greatly appreciated.