Incorrect Figure when using Group by Clause

[Anonymous]

hi,

I am encountering an issue, i have the below file:

Here is the below script:

LOAD

     DOCID,

     ITEMID,

     Num (Trim([QTY]),'#,##0', '.' , ',') as PRQTY

FROM

[..\..\QVData\QVLive\PRCODES_O.QVD]

(qvd)

WHERE PRCODE='O002';

However, what I am trying to do, is get the sum of PRQTY and Group it by DOCID.  When I try the below code, it does not work.

LOAD

     DOCID,

     ITEMID,

     sum(Num (Trim([QTY]),'#,##0', '.' , ',')) as PRQTY

FROM

(qvd)

WHERE PRCODE='O002'

group by DOCID,ITEMID;

Result is :

When I Exclude ITEMID in the script, the PRQTY amounts to  -3483 which is incorrect

It should be as it is in the first screencapture.  Ive tried Rangesum etc.  But this only results in the object no displaying any data.

Any advice?

Regards,

[sunny_talwar]

From what it seems that you have a combination of DOCID and ITEMID repeating 27 times in your dashboard.... that is why when you sum in the script you are getting 135 (5*27), 108 (4*27), 270 (10*27)... etc.... May be you need to add distinct? or use Only?

LOAD

    DOCID,

    ITEMID,

    Only(Num (Trim([QTY]),'#,##0', '.' , ',')) as PRQTY

FROM

(qvd)

WHERE PRCODE='O002'

group by DOCID,ITEMID;

or

LOAD

    DOCID,

    ITEMID,

    Sum(DISTINCT Num (Trim([QTY]),'#,##0', '.' , ',')) as PRQTY

FROM

(qvd)

WHERE PRCODE='O002'

group by DOCID,ITEMID;