topic Easy aggr and/or Distinct Sum question in QlikView

Easy aggr and/or Distinct Sum question

Thu, 16 Aug 2012 16:10:00 GMT

Another easy one for a newbie, all I need to do is sum up each distinct value in the column 'VALUE' for every distinct value in 'INDEX'. What formula will do this?

The Resulting table in QV should be:

Index Value

1 1

2 0

3 1

6 1

7 1

8 0

Thanks very much! I assume the answer will involve SUM, DISTINCT and AGGR somehow.

Re: Easy aggr and/or Distinct Sum question

swuehl — Thu, 16 Aug 2012 16:13:22 GMT

Try

=sum( aggr( Value, Index, Value))

The distinct part should be implicitely defined for the aggr() function.

Re: Easy aggr and/or Distinct Sum question

Thu, 16 Aug 2012 17:24:00 GMT

Thanks this definitely works! Although I am not sure why. Could you very briefly explain how it is operating on the data? Thanks again for such a quick answer!

Re: Easy aggr and/or Distinct Sum question

swuehl — Thu, 16 Aug 2012 21:57:07 GMT

The aggr() function will build like a local or temporary table, with the first argument (Value) being the table expression, all other arguments (Index, Value) the table dimensions. You should be able to create e.g. a straight table with dimensions Index and Value and expression =Value to get an idea of how this looks like. This temporary table returns a vector or array of expression results (or in other words, a column), one result per combination of dimension values. This array can then be aggregated again (here by using sum()).

Hope this helps,

Stefan

Re: Easy aggr and/or Distinct Sum question

Mon, 20 Aug 2012 15:14:17 GMT

Thanks again for the information. The expression works fine, unless i have greater than 1 million rows of data. It is painfully slow. I have tried to speed it up in various ways but nothing has been successful. Are there any other options that might run faster?

Re: Easy aggr and/or Distinct Sum question

swuehl — Mon, 20 Aug 2012 22:14:54 GMT

Maybe you could prepare everything in the script:

LOAD DISTINCT

Index, Value as Value2Sum

resident YourTable;

Then do a sum(Value2Sum) in your table chart with dimension Index (which is the key to YourTable, so it should be sensitive to selections affecting Index).

Re: Easy aggr and/or Distinct Sum question

Tue, 21 Aug 2012 15:04:38 GMT

Well, I am loading in 2 tables:

Example of first (every index value is unique, which is what i sum up):

Index Value

1 2

2 5

3 1

Then i left join to table #2 on Index:

Index Data1

1 a

1 b

2 a

3 c

3 e

etc.

So the resulting QV table ends up with the 'Value' being duplicated due to the left join of table 2.

I hope this is easy to understand and makes sense.

I removed the join, but then overall performance in the app was bad due to 2 huge tables (millions of records each) instead of 1 huge table.

So not sure if i can load distinct sense I need all these values. Or can I?

I really really appreciate your helping me out with this!

Re: Easy aggr and/or Distinct Sum question

swuehl — Tue, 21 Aug 2012 18:08:49 GMT

I assumed that you are JOIN tables here.

So you have millions of Index values? Well, I am not really sure what you want to achieve in the big picture, but if you want to keep the joined tables for performance (was this a big performance gain?), I would go for that, so left join your tables.

But if you want to speed up the sum up of values per index, I think the first table would be ideal for this, so why not keep this table (as I said, only the Index and Value renamed, so you have only Index ans key between tables).

I would assume that should not increase your memory consumption too much, but should really speed up your sum. (and QV only uses this table if you address the Value2Sum field, if you ask for the Value for any given Col1 / Col2, QV should use the left joined Value.

Hope this makes sense, but probably I just don't fully understand your complete requirements and setting.