topic Re: Using sum in preceding load in App Development

Using sum in preceding load

Eaun — Mon, 13 May 2019 15:08:43 GMT

My Load script is :

[INVENTROY_WITH_QUANTITY]:
LOAD
*,
NUM(SUM(TOTAL_WMS_COST),'£#,##0.00;-£#,##0.00') AS OVER_ALL_COST

Group By CINV_PART_NUMBER;

LOAD
*,
NUM(TOTQTY * WMS_COST,'£#,##0.00;-£#,##0.00') AS TOTAL_WMS_COST;

LOAD
TEXT(PARTNO) AS CINV_PART_NUMBER,
TOTQTY,
PLNCOD,
WHSCOD,
SYNONM,
CNTMST,
ApplyMap('MAP_COST2',PARTNO,0) AS WMS_COST,
LEFT(STKDTM,8) AS STKDTM,
LEFT(CRTDTM,8) AS CRTDTM;
SELECT
"PARTNO",
"PLNCOD",
"WHSCOD",
"SYNONM",
"PARTNO",
"CNTMST",
"TOTQTY",
"STKDTM",
"CRTDTM"
FROM "DC21WMS"."INVT_WITHQUANTITY_CINV";

but I get this error :

The error occurred here:

SELECT "PARTNO", "PLNCOD", "WHSCOD", "SYNONM", "PARTNO", "CNTMST", "TOTQTY", "STKDTM", "CRTDTM" FROM "DC21WMS"."INVT_WITHQUANTITY_CINV"

The following error occurred:

Invalid expression

The error occurred here:

Can not see what is wrong with it any ideas ?

Re: Using sum in preceding load

sunny_talwar — Mon, 13 May 2019 15:12:57 GMT

You are only Grouping by CINV_PART_NUMBER... but an aggregation in the script needs to be grouped by all of the non-aggregated dimensions. So, you can add all the dimensions to the group by or do load CINV_PART_NUMBER instead of load *

Re: Using sum in preceding load

Eaun — Mon, 13 May 2019 15:39:52 GMT

If I just replace * with CINV_PART_NUMBER I lose the other feilds and if I add all the dimensions to the group by I get the same error ?

Re: Using sum in preceding load

sunny_talwar — Mon, 13 May 2019 15:45:10 GMT

May be share few rows of data and the output you see from it?

Re: Using sum in preceding load

Eaun — Mon, 13 May 2019 15:57:47 GMT

What I'm trying to is replace this { SUM(AGGR(SUM(TOTAL_WMS_COST),PART_NUMBER)-Aggr(SUM(TOTAL_CIGMA_COST),PART_NUMBER)) } formula in the app and do the same calculation in the load script

the other load script is :

[MORE_GOODS_IN_CIGMA]:

LOAD *,
NUM(HM000_MOHTQ * CIGMA_COST,'£#,##0.00;-£#,##0.00') AS TOTAL_CIGMA_COST;

LOAD
text(ITNBR) AS HM000_PART_NUMBER,
HOUSE AS HM000_WAREHOUSE,
MOHTQ AS HM000_MOHTQ,
ApplyMap('MAP_COST',ITNBR,0) AS CIGMA_COST,
RECMO AS QTY_RECEIVED;

SQL SELECT
ITNBR,
HOUSE,
MOHTQ,
RECMO
FROM S44A3551.MURESLIB.HM000PR
WHERE "HOUSE" ='1'