Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

emyemyemy
New Contributor II

group by excel query

Hello every one ,

I want to group by my excel query  but when I reload my code :error shows Aggregation expressions required by GROUP BY clause !

here is my code  :


LOAD   ,
week, sum(production)

FROM
[path]
(
ooxml, embedded labels, table is mysheet) group by

Month(MakeWeekDate(year(Today()),week));

;

Any help plz thank you ,

1 Solution

Accepted Solutions
Highlighted
Frank_Hartmann
Honored Contributor II

Re: group by excel query

Group by only works if all fields which are not aggregated are within the Groupby statement. Otherwise you will get an error. Not sure if i understood your requirement correctly but give this a try:

TABLE1:

Load *, month(makeweekdate(2018,week)) as Month;

LOAD * INLINE [

    N°, week, production

    1, 22, 435

    2, 23, 354

    3, 22, 3245

    2, 23, 455

    8, 22, 324

    4, 23, 435

    5, 27, 4545

    6, 29, 324

    ];

  

left join

TABLE2:

Load Month, sum(production) as production1  Resident TABLE1 Group by Month; //DROP Table TABLE1;

Result will be this:

production1 Month
1244Jun
4004Mai
4869Jul
4 Replies
Frank_Hartmann
Honored Contributor II

Re: group by excel query

May be this:

LOAD    N°,

week,

sum(production) as Production_sum

FROM

[path]

(ooxml, embedded labels, table is mysheet) group by N°, week;

If you want to use aggregation functions like Sum() in your LOAD, you need to use a GROUP BY clause for all fields that are not embedded in aggregation functions.

emyemyemy
New Contributor II

Re: group by excel query

hi @Frank Hartmann    

Thank you  for your help and attention,

your suggestion is helping me  by reloading  my code successfully  ( error not shown) .

but it's not what I'm looking for : I need to group by my code with one field only  (group by  month from week field= Month(MakeWeekDate(year(Today()),week)) )

can you explain  to me  why I should write all   fields    that are not embedded in aggregation functions while it's not going to make any difference in query result  or  should I reorder  the group by clause , and second should I use aggregation function to can use group by clause  ?

Highlighted
Frank_Hartmann
Honored Contributor II

Re: group by excel query

Group by only works if all fields which are not aggregated are within the Groupby statement. Otherwise you will get an error. Not sure if i understood your requirement correctly but give this a try:

TABLE1:

Load *, month(makeweekdate(2018,week)) as Month;

LOAD * INLINE [

    N°, week, production

    1, 22, 435

    2, 23, 354

    3, 22, 3245

    2, 23, 455

    8, 22, 324

    4, 23, 435

    5, 27, 4545

    6, 29, 324

    ];

  

left join

TABLE2:

Load Month, sum(production) as production1  Resident TABLE1 Group by Month; //DROP Table TABLE1;

Result will be this:

production1 Month
1244Jun
4004Mai
4869Jul
emyemyemy
New Contributor II

Re: group by excel query

LOAD    N°,  month(makeweekdate(2018,week)) as Month ,  sum(production) as Production_sum

  FROM  [path]  (ooxml, embedded labels, table is mysheet)  ;

I got same result as you did here with out using group by  ^^ .

Thank you , your answer is brilliant  .