GroupBy in Load not working

[rahulhv1]

Hi Team,

below is the code i have written:

temp:

LOAD Capexno & [Capex Item No] as CapexKey,
if ([Approved By] = 'F', [System Date] ) as FinanceRelease,
if ([Approved By] = 'P', [System Date]) as PlantHeadRelease,
if ([Approved By] = 'B', [System Date]) as BusinessHeadRelease
FROM

(qvd) ;

above codes executes very well , but when i write below code , it gives error.

load CapexKey,
Financerelease, PlantHeadRelease,BusinessHeadRelease Resident temp group by CapexKey;

it give below error. Can any one pls. provied me the correct code

Error :

Aggregation expressions required by GROUP BY clause

load CapexKey,

Financerelease, PlantHeadRelease,BusinessHeadRelease Resident temp group by  CapexKey

[ashfaq_haseeb]

Hi,

You need to have some aggregation like count and sum.

What are you trying to achieve here.

Regards

ASHFAQ

[alexandros17]

When you group by, multiple values for Financerelease and other fields are "compressed" into the same CapexKey;

for this reason you need an aggregation function that sum (or find the max or somethimg else) all the values for the same CapexKey. This must be done even for string values, because if for the same CapexKey you have Value A and Value B which one will be taken?

So even in this case ypou must specify a rule ...

Let me know

[maleksafa]

when using group by , you need to have aggregation on the load fields.

example:

load CapexKey,

sum(Financerelease), max(PlantHeadRelease),count(BusinessHeadRelease) Resident temp group by CapexKey;

[ashfaq_haseeb]

Hi Apart from what safa had suggested.

If you want some fields to be excluded, then you have to use only function.

Look below

load CapexKey,

sum(Financerelease), max(PlantHeadRelease),only(BusinessHeadRelease) Resident temp group by CapexKey;

Regards

ASHFAQ

[its_anandrjs]

When ever using the Group By in the load script you have to use the Aggregation functions like Sum, Max, Count etc.

For your script try with

Let me know there is only one key and rest are date fields what result you are expecting.

[rahulhv1]

Very rightly said anand, there is only one key and other fields are Date. Ultimate result i want is as under

CapexKye     FinanceReleased  PlantHeadRelease  BusinessHeadRelease

011211           10-Jul-2014         11-Jul-2014           11-Jul-2014

022211           11-Jul-2014         12-Jul-2014           13-Jul-2014