Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
chrisg
Contributor II

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
michael123
Contributor

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
michael123
Contributor

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;

siddharth_kulka
Contributor

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

chrisg
Contributor II

Re: Group by - Syntax error in the code?

Thank you for the quick response.

Have a nice weekend!

best regards

Christoph

Community Browser