Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
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'
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 *
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 ?
May be share few rows of data and the output you see from it?
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'