topic Re: Join Question - Eliminate Duplicate Values in Sum in QlikView

Join Question - Eliminate Duplicate Values in Sum

Tue, 23 Oct 2012 19:28:54 GMT

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!

Re: Join Question - Eliminate Duplicate Values in Sum

Tue, 23 Oct 2012 19:35:29 GMT

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.

Re: Join Question - Eliminate Duplicate Values in Sum

Tue, 23 Oct 2012 19:37:14 GMT

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

Re: Join Question - Eliminate Duplicate Values in Sum

johnw — Tue, 23 Oct 2012 19:37:50 GMT

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.

Re: Join Question - Eliminate Duplicate Values in Sum

swuehl — Tue, 23 Oct 2012 19:40:53 GMT

Try

=sum(aggr(Number,Index))

Re: Join Question - Eliminate Duplicate Values in Sum

johnw — Tue, 23 Oct 2012 19:41:11 GMT

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.

Re: Join Question - Eliminate Duplicate Values in Sum

Tue, 23 Oct 2012 19:45:51 GMT

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

Re: Join Question - Eliminate Duplicate Values in Sum

Tue, 23 Oct 2012 19:47:32 GMT

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?

Re: Join Question - Eliminate Duplicate Values in Sum

johnw — Tue, 23 Oct 2012 19:50:52 GMT

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.

Re: Join Question - Eliminate Duplicate Values in Sum

Tue, 23 Oct 2012 19:51:31 GMT

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

Re: Join Question - Eliminate Duplicate Values in Sum

Tue, 23 Oct 2012 21:39:17 GMT

Well the aggr does work.....but running into the same issue I have had using it in the past, it is so darn slow. Would work great on thousands of rows, but I am working with tens of millions of rows so it just doesn't perform fast enough on the front end. I suppose I could load it in the script, but there are a lot of numbers that are impacted so many columns would need to have the aggr run against them, not very elegant.

Thanks for the suggestions though, probably back to the drawing board on the schema.

Re: Join Question - Eliminate Duplicate Values in Sum

swuehl — Tue, 23 Oct 2012 21:46:24 GMT

What are these index issues you were running into (in other words: what is the join really needed for)?

Re: Join Question - Eliminate Duplicate Values in Sum

Tue, 23 Oct 2012 22:05:57 GMT

Here is the structure, the index with the same name as the table is the primary key (SQL) in each table, the rest foreign keys. (hope you aren't sorry you asked! )

Table1

Table1Index

Table6Index

Table7Index

Table2

Table2Index

Table1Index

Table5Index

Table3

Table3Index

Table6Index

Table7Index

Table4

Table4Index

Table3Index

Table5Index

Table5

Table5Index

Table6Index

Table6

Table6Index

Table7Index

Table7

Table7Index

Tables 1-5 are data tables, 6 and 7 are fact tables. I was attempting to join table 1 to 2 and table 3 to 4 to get around an issue I was having with the key table getting too big and never completing the load.