topic How to use Sum and Group By in QlikView

How to use Sum and Group By

Wed, 23 May 2012 14:59:44 GMT

I am new at QlikView and need help translating the following Oracle SQL code to QlikView. I can get the code to work as long as I do not try to SUM and GROUP BY. Any suggestions of how to recode?

Here is the Oracle SQL:

SELECT DMDUNIT,

DMDGROUP,

LOC,

STARTDATE,

SUM(QTY)

FROM FCST

WHERE STARTDATE BETWEEN MYDATEMIN AND MYDATEMAX

AND STARTDATE IS NOT NULL

GROUP BY DMDUNIT, DMDGROUP, LOC, STARTDATE;

Below is the QV code that I have written so far that works. Now I need to implement the SUM on QTY.

TESTING:

LOAD DMDUNIT,
     DMDGROUP,
     LOC,
     Date(STARTDATE,'MM/DD/YYYY') AS [STARTDATE],
     QTY
;

SQL SELECT
      DMDUNIT,
      DMDGROUP,
      LOC,
      STARTDATE,
      QTY
    FROM SCPOMGR.FCST
    WHERE TO_CHAR(STARTDATE,'YYYYMMDD') BETWEEN $(vCurrDate) AND $(vEndDate)
      AND STARTDATE IS NOT NULL
;

Thank you!

How to use Sum and Group By

Wed, 23 May 2012 15:31:35 GMT

Hi,

try in this way:

LOAD DMDUNIT,
     DMDGROUP,
     LOC,
     Date(STARTDATE,'MM/DD/YYYY') AS [STARTDATE],
     QTY
;

SQL SELECT
      DMDUNIT,
      DMDGROUP,
      LOC,
      STARTDATE,
      SUM(QTY) AS SUMQTY
    FROM SCPOMGR.FCST
    WHERE TO_CHAR(STARTDATE,'YYYYMMDD') BETWEEN $(vCurrDate) AND $(vEndDate)
      AND STARTDATE IS NOT NULL
GROUP BY DMDUNIT, DMDGROUP, LOC, STARTDATE;

Hope it helps

Re: How to use Sum and Group By

Miguel_Angel_Baeyens — Wed, 23 May 2012 15:43:30 GMT

Hi,

Check the following script. Note that the GROUP BY and the SUM() are in the LOAD part, that is the set of expressions used by QlikView. The SQL part is what you send to the ODBC driver and the database. You can do that in two steps, by performing a resident load then a group by.

TESTING:
LOAD DMDUNIT,
     DMDGROUP,
     LOC,
     Date(STARTDATE,'MM/DD/YYYY') AS [STARTDATE],
     SUM(QTY) AS SUMQTY
GROUP BY DMDUNIT,
     DMDGROUP,
     LOC,
     [STARTDATE]
;
SQL SELECT
      DMDUNIT,
      DMDGROUP,
      LOC,
      STARTDATE,
      QTY
    FROM SCPOMGR.FCST
    WHERE TO_CHAR(STARTDATE,'YYYYMMDD') BETWEEN $(vCurrDate) AND $(vEndDate)
      AND STARTDATE IS NOT NULL
;

Hope that helps.

Miguel

How to use Sum and Group By

Wed, 23 May 2012 15:43:37 GMT

Thank you for your help. Your suggestion works perfectly.

How to use Sum and Group By

Wed, 23 May 2012 15:52:57 GMT

Thank you for your reply. Your suggestion also works perfectly.