Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

GROUP BY ERROR

Hello,

Can anybody help me on the error below?

Aggregation expressions required by GROUP BY clause.

Operational_Report:

LOAD

     'US' AS 'Region',

     [Delivery Number],

     [Customer Order],

     [Brand],

     [Items Number],

     [Packages Number],

     Date([Order Transmission Date]) AS [Data arrivo ordine],

     Date([Order Shipping Date]) AS [Data evasione ordine],

     [Actual Weight],

     [Billed Weight],

     [Billed cost],

     [Ship Via Method],

     [Customer Name],

     [Customer Last Name],

     [Customer Name] & [Customer Last Name] AS [Customer Full Name],

     [Destination town],

     [Ship To State],

     [Ship To Zip]

FROM [lib://Reportistica/@US ME - Operational Report/*.xls]

(biff, embedded labels, table is [Operational Report$])

GROUP BY [Delivery Number];

I would like to group this collection of Excel files (Operational Reports) by the Delivery Number.

The fact is that one Delivery Number may be present in more than 1 row if that Delivery has been shipped with 2 or more Packages.

For example:

       

Delivery NumberNumber of ItemsNumber of PackagesOrder Transmission DateOrder Shipping DateActual WeightBilled WeightBilled cost
218388422/21/20172/21/201715.4825.67
218388422/21/20172/21/201715.4825.67

Delivery Number 218388 is reported twice because it consisted of 2 packages.

However, I want to gather data by Delivery Number (218388) and keep 2 as Number of Packages, 4 as Number of Items, 15.4 as Actual Weight, 8 as Billed Weight, and 25.67 as Billed cost.

Practically speaking, I want to consider only 1 row per each Delivery Number.

Any suggestions on the matter will be highly appreciated.

Thank you,

Giuseppe

1 Solution

Accepted Solutions
RadovanOresky
Partner Ambassador
Partner Ambassador

Hi Guiseppe,

If you really don't want to aggregate any of the fields, than you can use DISTINCT qualifier after the LOAD, without the need to put GROUP BY statement:

Operational_Report:

LOAD DISTINCT

     'US' AS 'Region',

     [Delivery Number],

     ... rest of the fields...

You use GROUP BY only when you want to aggregate (summarize) some of the fields.

Hope this helps,

Radovan

View solution in original post

2 Replies
RadovanOresky
Partner Ambassador
Partner Ambassador

Hi Guiseppe,

If you really don't want to aggregate any of the fields, than you can use DISTINCT qualifier after the LOAD, without the need to put GROUP BY statement:

Operational_Report:

LOAD DISTINCT

     'US' AS 'Region',

     [Delivery Number],

     ... rest of the fields...

You use GROUP BY only when you want to aggregate (summarize) some of the fields.

Hope this helps,

Radovan

Anil_Babu_Samineni

You dont require group by here i believe. Group by world only aggregate functions only

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)