topic GroupBy in Load not working in QlikView

GroupBy in Load not working

rahulhv1 — Mon, 14 Jul 2014 11:57:17 GMT

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

Re: GroupBy in Load not working

ashfaq_haseeb — Mon, 14 Jul 2014 11:59:13 GMT

Hi,

You need to have some aggregation like count and sum.

What are you trying to achieve here.

Regards

ASHFAQ

Re: GroupBy in Load not working

alexandros17 — Mon, 14 Jul 2014 12:01:27 GMT

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

Re: GroupBy in Load not working

maleksafa — Mon, 14 Jul 2014 12:01:45 GMT

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;

Re: GroupBy in Load not working

ashfaq_haseeb — Mon, 14 Jul 2014 12:09:48 GMT

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

Re: GroupBy in Load not working

its_anandrjs — Mon, 14 Jul 2014 12:14:25 GMT

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.

Re: GroupBy in Load not working

rahulhv1 — Tue, 15 Jul 2014 03:45:42 GMT

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