Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
qlikmeplease
Contributor III

Incorrect Figure when using Group by Clause

hi,

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

1234.JPG

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 :

33333.JPG

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,

1 Reply

Re: Incorrect Figure when using Group by Clause

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;