Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello to all and thanks for taking the time to read/assist with this issue.
I have a sum that gives the right result in a table.
sum (([VGW03] / [BMSCH]) * [GMNGA]) /60
I want to create that value as a field I can use, so assumed I could do this in the load script.
When I try I get an 'invalid expression' load script error.
My load script is (have removed the full SQL address);
--------------------------------------------------------------------------------------------------------------
LIB CONNECT TO 'Microsoft_SQL_Server(uk_daryn.bray)';
LOAD
[MANDT],
[RUECK],
[RMZHL],
[VORNR],
Date(Date#([ERSDA], 'YYYYMMDD') ) AS [ERSDA],
[ERNAM],
Date(Date#([BUDAT], 'YYYYMMDD') ) AS [BUDAT],
[AFRU_TEXT],
[GMNGA],
[XMNGA],
[SMENG],
[ERZET],
[STOKZ],
[STZHL],
[AUFNR],
[PLGRP],
[DISPO],
[FEVOR],
Date(Date#([GLTRS], 'YYYYMMDD') ) AS [GLTRS],
Date(Date#([LTRMI], 'YYYYMMDD') ) AS [LTRMI],
[MATNR],
[DWERK],
[STATU],
[PLPO_TEXT],
[BMSCH],
[VGW02],
[VGW03],
[ARBID],
[ARBPL],
[KTEXT],
[MAKTX],
[PLANR],
[STPRS],
SUM (([VGW03] / [BMSCH]) * [GMNGA] /60) as [Line Hours],
// Lab (Min) / Base QTY * Confirmed QTY
if([LTRMI] > [GLTRS] ,'LATE',
if([LTRMI] < [GLTRS] ,'EARLY',
if([LTRMI] = [GLTRS] ,'ON TIME'))) as [Production Order Finish Status],
ApplyMap ('Map1', ([ARBPL]), '-Unkown-') as Category,
ApplyMap ('Map2', ([ARBPL]), '-Unkown-') as Category2,
ApplyMap ('Map3', ([FEVOR]), '-Unkown-') as SchedulerDesc,
ApplyMap ('Map4', ([FEVOR]), '-Unkown-') as Channel,
ApplyMap ('Map5', ([FEVOR]), '-Unkown-') as Product_Family_1;
[ZUK_CAPU]:
SELECT MANDT,
RUECK,
RMZHL,
VORNR,
ERSDA,
ERNAM,
BUDAT,
"AFRU_TEXT",
GMNGA,
XMNGA,
SMENG,
ERZET,
STOKZ,
STZHL,
AUFNR,
PLGRP,
DISPO,
FEVOR,
GLTRS,
LTRMI,
MATNR,
DWERK,
STATU,
"PLPO_TEXT",
BMSCH,
VGW02,
VGW03,
ARBID,
ARBPL,
KTEXT,
MAKTX,
PLANR,
STPRS
FROM "CO_DB_01".dbo."ZUK_CAPU" Where ([DWERK] ='UK01') AND (GMNGA <> 0);
--------------------------------------------------------------------------------------------------------------
The above script works okay without the line;
SUM (([VGW03] / [BMSCH]) * [GMNGA] /60) as [Line Hours],
It will also load with that line but without SUM in front of it (but the value of [Line Hours] is then not correct).
The error I get is below, but strangely does not appear to point at the SUM line?
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
Any help or advice would be appreciated, thanks in advance.
Regards Daryn
ps.. I also noticed that the error doesn't show the last ';' but it is in the loadscript. Assume that is just the way it reports.
FROM "CO_DB_01".dbo."ZUK_CAPU" Where ([DWERK] ='UK01') AND (GMNGA <> 0);
Hi
When you use aggregation function like sum, max, min, you need to use group by the remaining columns..
Hope it helps
Hi, you don't need to group this, just remove the SUM:
(([VGW03] / [BMSCH]) * [GMNGA]) /60 as [Line Hours]
You use group by when you want to aggregate something on one or more dimensions.
edit: missed this part: "It will also load with that line but without SUM in front of it (but the value of [Line Hours] is then not correct)."
Should work correct, what result do you get?
Hi
When you use aggregation function like sum, max, min, you need to use group by the remaining columns..
Hope it helps
Hi, many thanks for your response.
I hadn't used group by before, but think I have worked it out?
[Line Hours]:
NoConcatenate
Load
[VGW03],
[BMSCH],
[GMNGA],
SUM (([VGW03] / [BMSCH]) * [GMNGA]) /60 as [Line Hours]
// Lab (Min) / Base QTY * Confirmed QTY
Resident [ZUK_CAPU]
Group by [VGW03], [BMSCH], [GMNGA];
Though my calculation still looks to differ to if I just use
SUM (([VGW03] / [BMSCH]) * [GMNGA]) /60 as [Line Hours]
as a calculated measure in a table?
Any thoughts/ideas?
Again , thanks for your time and advice.
Regards Daryn
Hi, you don't need to group this, just remove the SUM:
(([VGW03] / [BMSCH]) * [GMNGA]) /60 as [Line Hours]
You use group by when you want to aggregate something on one or more dimensions.
edit: missed this part: "It will also load with that line but without SUM in front of it (but the value of [Line Hours] is then not correct)."
Should work correct, what result do you get?