Join Question - Eliminate Duplicate Values in Sum

[#1]

Quick (I hope) question.   For the load script below, I need to join 2 tables and have the value in "Number" NOT get duplicated.   Is there a way to do this?

Currently the Number column gets duplicated for due whenever there is more than 1 row in the second table.  

Desired results would be for there to only be one number 2 on Bob, index 1, instead of two number 2's as in the results below.  So in an expression, I need to sum the distinct Number column, which works if you incorporate Index somehow, so maybe the answer is the somehow write the sum to include Index without altering the output of the expression? 

Attached is this test scenario.   Hope this is easy. 

Thanks much! 

[#2]

If you only want one record for Bob, do you want to see AnyNumber 10 or 14?  For example, do you only want the max value? The min? The one to many relationship is causing your multiple records.

[#3]

I want to Sum(Number) for Bob, and have the answer be 9.   Currently it is 11 because of the duplicates.  

[johnw]

I'm unclear.  Did you want to sum AnyNumber?  In a chart:

Dimensions: Index, Number, Person
Expression: sum(AnyNumber)

In script, something like this replacing your second load:

OUTER JOIN (Table1)
LOAD Index, sum(AnyNum) as AnyNum
INLINE [
...

Edit:  OK, that's not what you want, though I still don't know what you DO want.

[swuehl]

Try

=sum(aggr(Number,Index))

[johnw]

It sounds like you just want to skip the outer join.  If you don't join, it won't duplicate the row, and if you make a chart of Person and sum(Number), you'll get 9 for Bob.

[#7]

Unfortuntately the join is needed due to index issues, but I hear you, I wish i could!

[#8 · Accepted solution]

Thanks, I just created a chart with Person as the dimension and tried this expression:

=sum(Aggr(Number,Person))

But it didn't work.   What do I need to change?

[johnw]

The expression uses Index, not Person.  Check what he wrote again, and copy it exactly.  It should work as long as the Index is uniquely identifying rows in the original table, pre-duplication.

[#10]

Got it, nevermind.   =sum(Aggr(Number,Index))