topic Re: group by excel query in QlikView

group by excel query

emyemyemy — Tue, 25 Sep 2018 14:08:14 GMT

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 N°,
week, sum(production)

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

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

;

Any help plz thank you ,

Re: group by excel query

Frank_Hartmann — Tue, 25 Sep 2018 15:29:15 GMT

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.

Re: group by excel query

emyemyemy — Wed, 26 Sep 2018 12:55:09 GMT

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 ?

Re: group by excel query

Frank_Hartmann — Wed, 26 Sep 2018 14:03:36 GMT

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
1244	Jun
4004	Mai
4869	Jul

Re: group by excel query

emyemyemy — Thu, 27 Sep 2018 12:46:26 GMT

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 .