SUM during load

Wed, 20 Jan 2010 17:55:59 GMT

Hello,

I'm a bit slow. Can somebody help me on summing during load.


ODBC CONNECT TO minitrac (XUserId is KBSeeUROeB, XPassword is GdfaIWFNGDdeWbFGUDbB);
GM:
LOAD `GL_Account`,
 TranDate,

 TranNo,
 `Control_No`,
 HistUpd,
 xglacctno,
 xtrandate,
 REGION,
 MAKE,
 MODEL,
 purgechar (SERIAL,'GM') as SERIAL,
 DIVISION,
 `HR_METER`,
 `LAST_HR_METER`,
 REV,
 REP,
 CUSTOMER,
 `GM_EXP`,
 `GM_START`,
 BRANCH,
 `UNIT_OPTIONS`,
 `GM_PAYMENT`,
 `LEASE_PAYMENT`,
 CAHRS,
 `LEASE_RESIDUAL`,
 `LEASE_EXPDATE`,
 `OT_RATE`,
 `OT_INTERVAL`,
 `CONTRACT_GP`',
 SUM(REV) as TOTAL_REV;

SQL SELECT *
FROM minitrac.mpglcn where Year(TranDate) <> "2030";

I get an error when the SUM(REV) as TOTAL_REV is in the load script. Am I doing something wrong? I did a lot of searches, and I think I have to calculate the SUM(REV) on another load statement. However, I'm not a SQL person by any means.

Any help is much appreciated!

SUM during load

johnw — Thu, 21 Jan 2010 02:12:57 GMT

To do a sum() or other aggregation expression in the script, you need a GROUP BY. So you'll need to group by that whole list of fields in the load statement. As an alternative, it might be more efficient to do the group by in the SQL rather than in the LOAD.

LOAD `GL_Account`,
TranDate,
...
TOTAL_REV
;
SQL SELECT
<all fields but REV>
,sum(REV) as TOTAL_REV
FROM minitrac.mpglcn
WHERE year(TranDate) <> "2030"
GROUP BY
<all fields but REV>
;

topic SUM during load in QlikView

SUM during load

SUM during load