topic Re: Simple sum/group by giving wrong results in QlikView

Simple sum/group by giving wrong results

Thu, 30 May 2013 07:42:22 GMT

Can someone give me a reason why the SumReadPassed column does not give me a correct result?

The Passed_both_num consists of only "0" and "1". Each Employee initials appear max. 4 times - but still my sum is returning values from 1 to 45 (should not be possible)

Anything wrong with my Group By / Sum script???

TABLE:

     LOAD [Employee ID],
     Name,
     Num#([Passed_both]) as Passed_both_num,
     [SOP ID],
     [SOP Task Code],
     [SOP Revision],
     [SOP Due Date],
     [SOP Sign Date],
     [SOP Taken On Time],
     [SOP Status],
     [Test ID]

FROM
\\BE_training.qvd
(qvd);

SUM:

LOAD[Employee ID],

SUM (Passed_both_num) AS SumReadPassed

RESIDENT
TABLE

GROUP BY[Employee ID];

COUNT:

LOAD[Employee ID],

TEXTCOUNT (DISTINCT ([SOP ID])) AS CountSOPID

RESIDENT
TABLE

GROUP BY[Employee ID];

Re: Simple sum/group by giving wrong results

Sokkorn — Thu, 30 May 2013 07:54:18 GMT

Hi gia_qlikview,

Just give script a try

SUM:
NoConcatenate

LOAD[Employee ID],

SUM (Passed_both_num) AS SumReadPassed

RESIDENT
TABLE

GROUP BY[Employee ID];

SUM:
NoConcatenate

LOAD[Employee ID],

SUM (Distinct Passed_both_num) AS SumReadPassed

RESIDENT
TABLE

GROUP BY[Employee ID];

Regards,

Sokkorn

Re: Simple sum/group by giving wrong results

anandathome — Thu, 30 May 2013 08:12:16 GMT

I don't think you explicitly need to convert [Passed_both] to Num.

Simple sum and group by would work.

In the table viewer, right click on "Sum" table and view data.

This should give you a clear view, where it is going wrong.

Re: Simple sum/group by giving wrong results

Thu, 30 May 2013 08:37:06 GMT

qvd file was incorrect.. but thanks anyway!

Re: Simple sum/group by giving wrong results

Sokkorn — Thu, 30 May 2013 08:38:52 GMT

Hi gia_qlikview,

That a problem. Great you can figure it out.

Cheer!!!

Sokkorn