How to use Sum and Group By

[#1]

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!

[#2 · Accepted solution]

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

[Miguel_Angel_Baeyens]

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

[#4]

Thank you for your help.  Your suggestion works perfectly.

[#5]

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