Group by

[marieclaire]

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

[qlikviewwizard]

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;

[vishsaggi]

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

[vishsaggi · Accepted solution]

‌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;

[jyothish8807]

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,

KC

[shiveshsingh]

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

[marieclaire]

Thank you for your help !

[marieclaire]

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

Anyway, thank you.

[marieclaire]

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

Regards,