Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Error when using group by clause

Can anyone tell me why i am getting the error over this query when i am trying to use group by clause in below query.. Kindly suggest me how should i modify this query in order to run it properly.

Thanks

LOAD

    SLUG,

    TIME,

    HOURS,

    Block,

    COUNT(LIH) as Sums

   

FROM [lib://Desktop/generate.xlsx]

(ooxml, embedded labels, table is Sheet1)

Group BY SLUG;

ERROR'S ARE ALSO ATTACHED.

1 Solution

Accepted Solutions
tinaco110
Contributor III

Re: Error when using group by clause

Muhammad,

All the columns appearing in your LOAD or SELECT clause should be included in your GROUP BY verb excluding the aggregated columns, like this:

LOAD

    SLUG,

    "TIME",

    "HOURS",

    Block,

    Count(LIH)      as    Sums

FROM [lib://Desktop/generate.xlsx]

(ooxml, embedded labels, table is Sheet1)

Group by SLUG, "TIME", "HOURS", Block;

HTH

6 Replies
oknotsen
Honored Contributor III

Re: Error when using group by clause

You will either have to group by slug, time, hours and block or remove time, hours and block in order for this to work.

May you live in interesting times!
vvvvvvizard
Contributor III

Re: Error when using group by clause

Try load *',

Count(lih) as sums

Resident tablename

Group by slug, time, hours,   block;

Change count(lih) as sum to just lih in the first table

tinaco110
Contributor III

Re: Error when using group by clause

Muhammad,

All the columns appearing in your LOAD or SELECT clause should be included in your GROUP BY verb excluding the aggregated columns, like this:

LOAD

    SLUG,

    "TIME",

    "HOURS",

    Block,

    Count(LIH)      as    Sums

FROM [lib://Desktop/generate.xlsx]

(ooxml, embedded labels, table is Sheet1)

Group by SLUG, "TIME", "HOURS", Block;

HTH

kkkumar82
Valued Contributor III

Re: Error when using group by clause

Hi Muhammad,

If you want the sum by SLUG then

Remove the group by for the Load and do a resident of it and do a group by using SLUG like below.

Data:

LOAD

    SLUG,

    "TIME",

    "HOURS",

    Block,

    Count(LIH)      as    Sums

FROM [lib://Desktop/generate.xlsx]

(ooxml, embedded labels, table is Sheet1);

Load

SLUG,

Count(LIH) as Sums

Resident Data

group by SLUG;

Not applicable

Re: Error when using group by clause

Thanks for the participation.

but still getting the error when using the query.

Not applicable

Re: Error when using group by clause

Thanks for the participation .. it's working !!

But one thing more i want to know that i am using group by clause not to repeat the same data in slug field but after using this query getting the repetition of data .. any suggestion ?

i dont want to repeat the same data more than one time