Group by - Syntax error in the code?

[chrisg]

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

[michael123 · Accepted solution]

Use preceiding load:

LOAD *

WHERE NumOccures>=2;

LOAD

       ID,

       Count(ID) AS NumOccures

Resident Product

//where count(ID) >=2

Group by ID;

[siddharth_kulka]

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]

Thank you for the quick response.

Have a nice weekend!

best regards

Christoph