topic Re: Group by - Syntax error in the code? in QlikView

Group by - Syntax error in the code?

chrisg — Fri, 24 Aug 2012 13:31:21 GMT

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

Re: Group by - Syntax error in the code?

michael123 — Fri, 24 Aug 2012 13:54:40 GMT

Use preceiding load:

LOAD *

WHERE NumOccures>=2;

LOAD

ID,

Count(ID) AS NumOccures

Resident Product

//where count(ID) >=2

Group by ID;

Re: Group by - Syntax error in the code?

siddharth_kulka — Fri, 24 Aug 2012 13:57:38 GMT

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

Re: Group by - Syntax error in the code?

chrisg — Fri, 24 Aug 2012 14:30:46 GMT

Thank you for the quick response.

Have a nice weekend!

best regards

Christoph