Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
ranibosch
Creator
Creator

Formula in script

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

Labels (2)
1 Solution

Accepted Solutions
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

1 Reply
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)