topic Group by in QlikView

Group by

marieclaire — Wed, 25 Nov 2020 16:16:04 GMT

Hi the community,

I need to build a table with the sum of "visites" for each user "IDSSO".

Each "IDSSO" has several "cookies".

Each "IDSSO" makes several visits each month.

I tried to build the table "IDSSO" with a group by, but it does not work.

Any idea ?

--------------------------------------------------

Cookies:

Cookies_Editeur,

Cookies_Year,

Cookies_Month,

Cookie,

Cookies_IDSSO,

Cookies_Visites

FROM (XmlSimple, Table is [DataFeed/DataSet/Rows/Row]);

IDSSO:

LOAD

Cookies_Editeur,

Cookies_Year,

Cookies_Month,

Cookie,

Cookies_IDSSO,

sum(Cookies_Visites) as Cookies_Visites

Resident Cookies

group by Cookies_Editeur,

Cookies_IDSSO

;

drop Table Cookies;

-----------------------------------------

Thank you for your help

Re: Group by

qlikviewwizard — Mon, 28 May 2018 22:00:54 GMT

Hi ,

Try like this.

Cookies:

Cookies_Editeur,

Cookies_Year,

Cookies_Month,

Cookie,

Cookies_IDSSO,

Cookies_Visites

FROM (XmlSimple, Table is [DataFeed/DataSet/Rows/Row]);

NoConcatenate

IDSSO:

LOAD

Cookies_Editeur,

Cookies_Year,

Cookies_Month,

Cookie,

Cookies_IDSSO,

sum(Cookies_Visites) as Cookies_Visites

Resident Cookies

group by Cookies_Editeur,

Cookies_IDSSO

;

drop Table Cookies;

Re: Group by

vishsaggi — Mon, 28 May 2018 22:16:37 GMT

‌i believe this will throw Group By error as all the non aggr fields must be provide in the group by list.

Re: Group by

vishsaggi — Mon, 28 May 2018 22:19:20 GMT

‌mMay be try this?

Cookies:

Cookies_Editeur,

Cookies_Year,

Cookies_Month,

Cookie,

Cookies_IDSSO,

Cookies_Visites

FROM (XmlSimple, Table is [DataFeed/DataSet/Rows/Row]);

Left Join(Cookies)

IDSSO:

LOAD

Cookies_IDSSO,

sum(Cookies_Visites) as Cookies_Visites

Resident Cookies

group by

Cookies_IDSSO

;

drop Table Cookies;

Re: Group by

jyothish8807 — Tue, 29 May 2018 04:30:00 GMT

Try This: (While using group by , all the non aggregated fields should be added under group by clause.

Cookies:

Cookies_Editeur,

Cookies_Year,

Cookies_Month,

Cookie,

Cookies_IDSSO,

Cookies_Visites

FROM (XmlSimple, Table is [DataFeed/DataSet/Rows/Row]);

Noconcatenate

IDSSO:

LOAD

Cookies_Editeur,

Cookies_Year,

Cookies_Month,

Cookie,

Cookies_IDSSO,

sum(Cookies_Visites) as Cookies_Visites

Resident Cookies

group by

Cookies_Editeur,

Cookies_Year,

Cookies_Month,

Cookie,

Cookies_IDSSO

;

drop Table Cookies;

Br,

Re: Group by

shiveshsingh — Tue, 29 May 2018 04:38:30 GMT

you need to put all the non aggregated fields under group by clause as suggested in above comment.

Re: Group by

marieclaire — Tue, 29 May 2018 12:52:47 GMT

Thank you for your help !

Re: Group by

marieclaire — Tue, 29 May 2018 12:53:42 GMT

Thank you but i do not manage to make it work.

Anyway, thank you.

Re: Group by

marieclaire — Tue, 29 May 2018 12:55:30 GMT

Thank you but i can not make it work with this solution.

Regards,