Group by is invalid

[Cassie_L]

Dear all,

I found out in front-end, amount was not auto-sum by every dimension. Like if I use "functional area" as first dimension, I could find the total amount. If I use "local currency" as first dimension, the total amount shown "0".

So I applied "group by" as following, basically group by all dimensions I will upload. But it still turns out that the same result, most of dimension could not get total amount calculation.

Sum(Amount_in_Transaction_Currency) as "Amount in Transaction Currency",

Sum(Amount_in_Local_Currency) as "Amount in LC",

Sum(Amount_in_Operating_Concern_Currency) as "Amount in USD",

Sum(Amount_in_Group_Currency) as "Amount in Group Currency",

Sum(Amount_in_Another_Currency) as "Amount in Another Currency",

Resident ZFAC_Temp2

Where

Amount_in_Transaction_Currency<>0 or Amount_in_Local_Currency <>0 or Amount_in_Operating_Concern_Currency<>0 or Amount_in_Group_Currency<>0 or Amount_in_Another_Currency<>0 or Amount_in_Original_Transaction_Currency<>0

Group by

"Document Number", "Ledger", "Company Code", "Line item", "Transaction", "Transaction Type", "Transaction Currency", "Base Unit of Measure", "Reference Transaction", "Record Type", "Version", "Logical system", "Account Number", "Cost Element", "Cost Center", "Profit Center", "Functional Area ID", "Business Area", "Controlling Area", "Segment", "Parent Profit Center", "Parent Functional Area", "Trading Partner", "Partner Segment", "Quantity", "Debit/Credit Ind.", "Posting Period", "Original Transaction Currency", "Fiscal Year", "Posting Date", "Line Item", "Accounting Document Number", "Posting Key", "Doc. status", "Item category", "User name", Month, Quarter, Region, "Functional Area", "GL Account", "Local Currency", FX;

Drop Table ZFAC_Temp2;

#Groupby #Groupbyinvalid

[BrunPierre]

 You're probably missing field(s)/dimensions in the group by condition. Also, there should be no comma at the end of this line.

...

Sum(Amount_in_Another_Currency) as "Amount in Another Currency",

Sum(Amount_in_Another_Currency) as "Amount in Another Currency"

Resident ZFAC_Temp2

...

[Cassie_L]

Hi, Pierre,

Thank you.

So if I missed field in group by condition, other dimensions could apply to group by functions as well?

I delete script between sum and resident, so there is no issue for the comma.

[BrunPierre]

Try this

LOAD "Document Number", "Ledger", "Company Code", "Line item", "Transaction", "Transaction Type", "Transaction Currency",
"Base Unit of Measure", "Reference Transaction", "Record Type", "Version", "Logical system", "Account Number", "Cost Element", 
"Cost Center", "Profit Center", "Functional Area ID", "Business Area", "Controlling Area", "Segment", "Parent Profit Center",
"Parent Functional Area", "Trading Partner", "Partner Segment", "Quantity", "Debit/Credit Ind.", "Posting Period", 
"Original Transaction Currency", "Fiscal Year", "Posting Date", "Line Item", "Accounting Document Number", "Posting Key",
"Doc. status", "Item category", "User name", Month, Quarter, Region, "Functional Area", "GL Account", "Local Currency", FX,
Sum(Amount_in_Transaction_Currency) as "Amount in Transaction Currency",
Sum(Amount_in_Local_Currency) as "Amount in LC",
Sum(Amount_in_Operating_Concern_Currency) as "Amount in USD",
Sum(Amount_in_Group_Currency) as "Amount in Group Currency",
Sum(Amount_in_Another_Currency) as "Amount in Another Currency"

Resident ZFAC_Temp2
Where Amount_in_Transaction_Currency<>0 
or Amount_in_Local_Currency <>0 
or Amount_in_Operating_Concern_Currency<>0 
or Amount_in_Group_Currency<>0 
or Amount_in_Another_Currency<>0 
or Amount_in_Original_Transaction_Currency<>0

Group by "Document Number", "Ledger", "Company Code", "Line item", "Transaction", "Transaction Type", "Transaction Currency",
"Base Unit of Measure", "Reference Transaction", "Record Type", "Version", "Logical system", "Account Number", "Cost Element", 
"Cost Center", "Profit Center", "Functional Area ID", "Business Area", "Controlling Area", "Segment", "Parent Profit Center",
"Parent Functional Area", "Trading Partner", "Partner Segment", "Quantity", "Debit/Credit Ind.", "Posting Period", 
"Original Transaction Currency", "Fiscal Year", "Posting Date", "Line Item", "Accounting Document Number", "Posting Key",
"Doc. status", "Item category", "User name", Month, Quarter, Region, "Functional Area", "GL Account", "Local Currency", FX;

Drop Table ZFAC_Temp2;

[Cassie_L]

Hi, Pierre, thank you, this is exactly my original script. And in the front end, I cannot sum by most of dimensions like company code, local currency.