Formula in script

[ranibosch]

Good day,

I hope someone can help me here. What is wrong with my script highlighted in red?

[TEMP Sales Value Entries]:

LOAD
"Item No_" as "Item No",
Year("Posting Date") as [Value Entry Year],
Month("Posting Date") as [Value Entry Month],
"Sales Amount (Actual)",
"Global Dimension 2 Code" as BU,
"Cost Amount (Actual)";

SQL SELECT
"Item No_",
"Posting Date",
"Sales Amount (Actual)",
"Global Dimension 2 Code",
"Cost Amount (Actual)",
"Document Type"
"Gen_ Prod_ Posting Group"

FROM $Value Entry"
Where "Document Type" in ('2','3')
and "Global Dimension 2 Code" not in ('QA','NPD')
and "Gen_ Prod_ Posting Group" in ('FG')
and "Posting Date" > = '01/01/2019';

Left Join ([TEMP Sales Value Entries])

LOAD
"No_" as "Item No",
Description as [Item Description],
"Routing No_" as "Routing No";

SQL SELECT
"No_",
Description,
"Routing No_"
FROM $Item";

[Sales Value Entries]:

LOAD
"Item No",
[Value Entry Year],
[Value Entry Month],
sum("Sales Amount (Actual)") + sum("Cost Amount (Actual)") / sum("Sales Amount (Actual)") as [Gross Profit],
BU,
[Item Description],
"Routing No"

Resident [TEMP Sales Value Entries];
DROP Table [TEMP Sales Value Entries];

[Anil_Babu_Samineni · Accepted solution]

Since you need aggregate function like Sum,Count, Max, Min, Avg etc. You must define the "Group By" clause to group the element to the aggregate for non aggregate fields like

[Sales Value Entries]:

LOAD
"Item No",
[Value Entry Year],
[Value Entry Month],
sum("Sales Amount (Actual)") + sum("Cost Amount (Actual)") / sum("Sales Amount (Actual)") as [Gross Profit],
BU,
[Item Description],
"Routing No"

Resident [TEMP Sales Value Entries] Group By "Item No",
[Value Entry Year],
[Value Entry Month],

BU,
[Item Description],
"Routing No";