topic Re: SUM calculates incorrect values in QlikView

SUM calculates incorrect values

Sat, 20 Dec 2014 21:12:58 GMT

Hi,

J'ai besoin de votre aide.

Je veux créer une table pour compter les valeurs d'une autre table .

Je ai essayé SUM mais je ne comprends pas pourquoi le résultat est incorrect .

My master table :

Key	Valn	ValNM1	ValNM2
EBB372	0	1	0
EBB372	1	0	0
EPP156	1	1	0
EPP156	1	0	1
EPP156	1	1	0

The result that i want :

Key	TOTALValn	TOTALValNM1	TotalValNM2
EBB372	1	1	0
EPP156	3	2	1

I've tried :

TableTest:

Load Key as NewKey,

Sum(Valn) as TOTALValn,

Sum(ValNM1) as TOTALValNM1,

Sum(ValNM2) as TOTALValNM2

Resident MasterTable

Group By Key;

But the result is not exact for all rows calculated and i don't understand why.

For example, SUM of KEY for EBB372 give : 3...

Do you have an idea?

Thanx for your help.

Re: SUM calculates incorrect values

anbu1984 — Mon, 22 Dec 2014 09:10:55 GMT

It works as expected.

Load Key as NewKey, Sum(Valn) as TOTALValn,

Sum(ValNM1) as TOTALValNM1,

Sum(ValNM2) as TOTALValNM2

Group By Key;

LOAD Key,

Valn,

ValNM1,

ValNM2

FROM

[http://community.qlik.com/thread/146438]

(html, codepage is 1252, embedded labels, table is @1);

Re: SUM calculates incorrect values

its_anandrjs — Mon, 22 Dec 2014 09:29:58 GMT

Hi,

In place of the Sum you can use count

MasterTable:
LOAD * Inline
[
Key,    Valn,    ValNM1,    ValNM2
EBB372,    0,    1,    0
EBB372,    1,    0,    0
EPP156,    1,    1,    0
EPP156,    1,    0,    1
EPP156,    1,    1,    0
];


LOAD
Key,
Count(if(Valn=1, Valn)) as TOTALValn,
Count(if(ValNM1=1,ValNM1)) as TOTALValNM1,
Count(if(ValNM2=1,ValNM2)) as TOTALValNM2
Resident MasterTable Group By Key;


DROP Table MasterTable;

Regards

Anand

Re: SUM calculates incorrect values

tyagishaila — Mon, 22 Dec 2014 10:10:52 GMT

Can you upload your data file,

I think your result value is okay, there may be a problem with Valn, ValNM1 and ValNM2 values in table.

It will show only those values which are distinct if any two rows for a key are same it shows it only once, but sum(field) is correct.

Check it.

Re: SUM calculates incorrect values

tyagishaila — Mon, 22 Dec 2014 11:06:15 GMT

Just add expression

count(key) in your table and check whether you get all values in table or not.

if you find count(key)>1 it means your values are repeated and not showing in table,

Re: SUM calculates incorrect values

jyothish8807 — Mon, 22 Dec 2014 11:15:30 GMT

Hi,

Why don't you directly create a straight chart with:

Dimension: Key

Exp1: sum(Valn)

Exp2: sum(ValNM1)

Exp3:sum(ValNM2)

Regards

Re: SUM calculates incorrect values

Mon, 22 Dec 2014 11:36:52 GMT

Hello,

Thanx for your help.

You are right!!!

The probleme was with my Qvd file.

But i don't understand why values was dupicates and not showing in the table...

Problem is resolved.

thank you all.

Re: SUM calculates incorrect values

jyothish8807 — Mon, 22 Dec 2014 11:51:43 GMT

Hi,

this may be because all the entries corresponding to that key may be identical to other entries.In that case it will show only one entry.

try like dis:

load

recno() as sino,

key,

...

from<>

then you can track your duplicate entries.

Regards