Formula in script

ranibosch — Sat, 16 Nov 2024 01:43:36 GMT

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];

Re: Formula in script

Anil_Babu_Samineni — Wed, 11 Dec 2019 08:10:55 GMT

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]:

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

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

topic Formula in script in QlikView

Formula in script

Re: Formula in script