topic Re: Resident and Group by in QlikView

Resident and Group by

Tue, 31 Jul 2012 11:53:04 GMT

Hello!

I get a invalid expression error on this scipt when trying to use sum and group by and its driving me insane. Any help?

VAtmp:

LOAD

     PART_NO,
     CONTRACT,
     ISSUE_QTY,
     Month
FROM
Z:\xxxx\xxxx\Extracted\Sup.QVD
(qvd);

VA:

LOAD

    PART_NO,
     CONTRACT,
     sum(ISSUE_QTY) as sumQTY,
     Month

Resident VAtmp Group by PART_NO;

Re: Resident and Group by

swuehl — Tue, 31 Jul 2012 12:01:27 GMT

You need to either use an aggregation function around CONTRACT (could be something like concat(CONTRACT) as concatCONTRACT, only(CONTRACT) as CONTRACT or count(CONTRACT) as countCONTRACT or whatever appropriate)

or add CONTRACT to your group by fields (...group by PART_NO, CONTRACT; )

Re: Resident and Group by

daveamz — Tue, 31 Jul 2012 13:12:53 GMT

Hello,

You should group by all non-calculated fields:

VA:

LOAD

    PART_NO,
     CONTRACT,
     sum(ISSUE_QTY) as sumQTY,
     Month

Resident VAtmp Group by PART_NO, CONTRACT, Month;