Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Partner
Partner

Group by - Syntax error in the code?

Hi,

I have a syntax error in the code. But unfortunately I can not find the error.

Product:

LOAD * INLINE [

    ID, Artikel

    1, Tee

    2, Milk

    3, Butter

    4, Corn

    5, Burger

    6, Ham

    7, Salt

    7, Salat

    9, Pasta

    8, Cake

    9, Apple

    10, Ice

    9, Water

];

LOAD

       ID,

       Count(ID) AS NumOccures

Resident Product where Count(ID)>=2 Group by ID;

Any hints?

Many Thx

Christoph

Tags (3)
1 Solution

Accepted Solutions
Partner
Partner

Re: Group by - Syntax error in the code?

Use preceiding load:

LOAD *

WHERE NumOccures>=2;

LOAD

       ID,

       Count(ID) AS NumOccures

Resident Product

//where count(ID) >=2

Group by ID;

3 Replies
Partner
Partner

Re: Group by - Syntax error in the code?

Use preceiding load:

LOAD *

WHERE NumOccures>=2;

LOAD

       ID,

       Count(ID) AS NumOccures

Resident Product

//where count(ID) >=2

Group by ID;

Highlighted
siddharth_kulka
Contributor II

Re: Group by - Syntax error in the code?

Hi Chris,

You cannot use an aggregation expression in the where clause.

You will need to break this into 2 steps, either with a resident or prefix load

Check below workaround:

Product:

LOAD * INLINE

[     ID, Artikel    

1, Tee     2, Milk     3, Butter     4, Corn     5, Burger     6, Ham     7, Salt     7, Salat     9, Pasta     8, Cake     9, Apple     10, Ice     9, Water

];

Final:

load *

where NumOccures>=2;

noconcatenate LOAD       

ID,       

Count(ID) AS NumOccures

Resident Product 

Group by ID;

Regards,

Siddharth

Partner
Partner

Re: Group by - Syntax error in the code?

Thank you for the quick response.

Have a nice weekend!

best regards

Christoph