why is this an invalid expression? (Concat Distinct, group by, order by)

[LiquidSword]

Why is the first load an invalid expression? and how do I fix it?

RemoveDuplicate:

load 

Distinct InterDupeSequence as NewSequence,

    Customer_Code,

    CrossSales_Date,

    CrossSales_Key,

    CrossSales_Key_v2,

    Cross_Cate;

load 

concat(distinct Category, ',', CatOrder) as InterDupeSequence,

    Customer_Code,

    CrossSales_Date,

    CrossSales_Key,

    CrossSales_Key_v2,

    Cross_Cate

Resident StanLines2

group by LineNum

order by LineNum,CatOrder;

drop table StanLines2;

[Lech_Miszkiewicz · Accepted solution]

Hi @LiquidSword 

For starters your load using aggregation function (not the preceeding) is wrong as group by needs to contain all fields which aren't using grouping function so it should be:

Load

    concat(distinct Category, ',', CatOrder) as InterDupeSequence,

    Customer_Code,

    CrossSales_Date,

    CrossSales_Key,

    CrossSales_Key_v2,

    Cross_Cate

Resident StanLines2

group by

    Customer_Code,

    CrossSales_Date,

    CrossSales_Key,

    CrossSales_Key_v2,

    Cross_Cate

order by

    LineNum,CatOrde

;