topic Group by is invalid in App Development

Group by is invalid

Cassie_L — Thu, 17 Nov 2022 14:50:10 GMT

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

Re: Group by is invalid

BrunPierre — Fri, 18 Nov 2022 08:05:43 GMT

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

...

Re: Group by is invalid

Cassie_L — Fri, 18 Nov 2022 09:34:16 GMT

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.

Re: Group by is invalid

BrunPierre — Fri, 18 Nov 2022 10:09:42 GMT

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;

Re: Group by is invalid

Cassie_L — Mon, 21 Nov 2022 06:56:24 GMT

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.