topic Group By function with sum in QlikView

Group By function with sum

Wed, 18 Jun 2014 13:27:10 GMT

Hi guys,

I would like to use the group by function with aggregate function as sum.

lets say the input is

Name age class Score

a 6 1 4

a 6 1 3

a 6 1 5

The desired output is

Name age class Score

a 6 1 12

The script I tried:

Load Name,

age,

class,

Sum(Score) as Score

from samplefile.csv

group by Name;

But the execution fails with error "Invalid expression"

Kindly Help Me out...

Re: Group By function with sum

MK_QSL — Wed, 18 Jun 2014 13:27:46 GMT

Load Name,

age,

class,

Sum(Score) as Score

from samplefile.csv

group by Name,age,class;

Update:

=====================================

Temp:

Load * Inline

[

Name, age, class, Score

a, 6 , 1, 4

a, 6 , 1, 3

a, 6 , 1, 5

];

T2:

Load

Name,

age,

class,

SUM(Score)

Resident Temp

Group By Name,age,class;

Drop Table Temp;

Re: Group By function with sum

Wed, 18 Jun 2014 13:30:30 GMT

Hi thank you for the prompt answer. But in case i dont want the class field in output, do i still need to group by all the fields preceeding the aggregate function

Re: Group By function with sum

its_anandrjs — Wed, 18 Jun 2014 13:33:09 GMT

You have to group by all the fields in the table see the script and load according to that

Load Name,

age,

class,

Sum(Score) as Score

from samplefile.csv

group by Name,age,class;

Re: Group By function with sum

MK_QSL — Wed, 18 Jun 2014 13:34:00 GMT

T2:

Load

Name,

age,

SUM(Score)

Resident Temp

Group By Name,age;

Re: Group By function with sum

its_anandrjs — Wed, 18 Jun 2014 13:36:51 GMT

Then not use class field in the load script do like

Load Name,

age,

//class,

Sum(Score) as Score

from samplefile.csv

group by Name,age;

Re: Group By function with sum

Anonymous — Wed, 18 Jun 2014 13:45:49 GMT

Hi Paromita,

All the fields mentioned inside the load statement along with sum function should(compulsory) also be mentioned in the group by clause.

But All fields which are mentioned in group by clause need not be used in load statement.(only if require u mention the field in the load statement else not required).

Re: Group By function with sum

Wed, 18 Jun 2014 13:50:55 GMT

Thanks guys... Problem Solved