Wrong sum? What am I doing wrong?

[#1]

Suppose we have two tables in our database:

contracts: contractid, username, productid;

prices: productid, price;

Suppose we have these records:

Table "contacts":

1,'peter,'a'

2,'peter','a'

3,'lois','b'

4,'lois','c'

5,'lois','c'

6,'stewie','a'

prices:

'a',1

'b',2

'c',3

Now you can see that:

Peter has 2 contracts of type 'a' => 2 points;

Lois has 1 contract of type 'b' => 2 point, 2 contracts of type 'c' => 6 points, total: 8 points,

Stewie has 1 contract of type 'a' => 1 point.

If I load these data in QlikView in two separate tables (without sql joins), I see the tables joining the records with the "contractid" field, and this is right.

I create a linear table, and chose the username for the dimension, then I create two expressions:

COUNT(contractid) for knowing how many contracts has every username,

SUM(price) for knowing the total price for all contracts of every user.

The count seems to be right, but the sum is wrong:

Peter has 2 contracts and 1 as total price,

Lois has 3 contracts and 5 as total price,

Stewie has 1 contract and 1 total price.

Why is QlikView summing only different contractid?? It seems that QlikView is doing the sum() only for different contractid for every username.

I've tried the TOTAL, TOTAL <username>, TOTAL <username, contractid>, but it doesn't woek.

If I do a SQL join at source, the results are right.

What am I doing wrong?

[#2]

Hello Darkbyte,

I assume you are an RDBMS guy speaking SQL perfectly. So you derived the automatic linking (by same fieldnames) of two QV-Tables being the same as an SQL-Join. But this is not quite true. I also had (and have) this way of thinking. QV stores its data not in relational tables you are familar, even if it looks like. Pls refer to your Manual or Help to read more about this.

darkbyte wrote:I see the tables joining the records with the "contractid" field, and this is right.

Isn't it the "productid" ? If so, don't let QV link both tables unless in both tables the fields are unique. Use the key word "join" instead. It would be too long to explain more about "join, keep and concatenate" here en details but to answer at least your posted question(s):

contracs:
LOAD * Inline [
ID, Name, ProductID
1,'peter','a'
2,'peter','a'
3,'lois','b'
4,'lois','c'
5,'lois','c'
6,'stewie','a'
];
prices:
Join(contracs)
LOAD * Inline [
ProductID, Price
'a',1
'b',2
'c',3
];

Try this little script with(out) the QV-join, take a look into the table viewer and start playing around using straight tables, tableboxes and so on. And don't hesitate to ask again.

Regards, Roland

[#3]

Hi Darkbyte,

The reason you are seeing those totals, is because QlikView is evaluating the expression over the available data in the table that contains the price field. QlikView will find the rows associated with each person and evaluate your expression. So, using lois as an example, she has one association to 'b' and two associations to 'c', however there is only a single price against 'b' and a single price against 'c'. This means that QlikView will evaluate the Sum of 2 and 3. Which gives you 5.

In order to change this behaviour you can do one of two things. Either join the 2 tables together which will create the necessary additional rows to give you the total you are looking for. Or, in your example above, you could try the following expression to force the additional associations to be created specifically for that expression

=Sum(aggr(Sum(price),id))

I hope this makes sense, it's a difficult concept to explain.

Thanks

Nick

[Anonymous]

Thank you Nick It is just what I want.

[#5]

Ok thanks!!! I was seeing the QV tables just like DB tables.

Now I understand better how the table joins work.

[Anonymous]

Thank you very much! I helped a lot.