Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

JustinDallas
Valued Contributor II

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?

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

Any help is greatly appreciated.

1 Solution

Accepted Solutions
Employee
Employee

Re: Join Many-to-One, Keep Calculation

Maybe this can return what you need

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

4 Replies
atkinsow
Valued Contributor II

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 
Employee
Employee

Re: Join Many-to-One, Keep Calculation

Maybe this can return what you need

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

JustinDallas
Valued Contributor II

Re: Join Many-to-One, Keep Calculation

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

JustinDallas
Valued Contributor II

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.

Community Browser