Incorrect Figure when using Group by Clause

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Incorrect Figure when using Group by Clause

sunny_talwar — Wed, 26 Apr 2017 12:43:51 GMT

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;