Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SUM calculates incorrect values

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 :

KeyValnValNM1ValNM2
EBB372010
EBB372100
EPP156110
EPP156101
EPP156110

The result that i want :

KeyTOTALValnTOTALValNM1TotalValNM2
EBB372110
EPP156321

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.

1 Solution

Accepted Solutions
tyagishaila
Specialist
Specialist

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,

View solution in original post

7 Replies
anbu1984
Master III
Master III

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);

its_anandrjs

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

tyagishaila
Specialist
Specialist

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.

tyagishaila
Specialist
Specialist

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,

jyothish8807
Master II
Master II

Hi,

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

Dimension: Key

Exp1: sum(Valn)

Exp2: sum(ValNM1)

Exp3:sum(ValNM2)

Regards

KC

Best Regards,
KC
Not applicable
Author

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.

jyothish8807
Master II
Master II

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

KC

Best Regards,
KC