Load Sum invalid expression

[Luca_perdic]

Hi community!

I cannot solve this that seems a so stupid problem but maybe is there something i am missing.

When i try to load a table with the sum(field) formula i get the error "Invalid expression"

Load Distinct
Confirmation_Code,
Confirmation_Flag_Final,
sum(Confirmation_Qty_Posted) as [Daily Qty Confirmed],
sum(Confirmation_Qty_ActScrap) as [Daily Qty Scrap Confirmed]

Inline [
Confirmation_Code, Confirmation_Flag_Final, Confirmation_Qty_Posted, Confirmation_Qty_ActScrap
1 , X , 2 , 4
2 , , 3, 4
3 , , 4 , 5
3, , 4,5];

[stevejoyce · Accepted solution]

You need to have a "group by".  Any field that is not in an aggregation needs to be listed in the group by or you will get this error.  Also group by cannot be done in a preceeding load, load your data first then resident the table.

Load Distinct
Confirmation_Code,
Confirmation_Flag_Final,
sum(Confirmation_Qty_Posted) as [Daily Qty Confirmed],
sum(Confirmation_Qty_ActScrap) as [Daily Qty Scrap Confirmed]

group by

Confirmation_Code,
Confirmation_Flag_Final

;

[Luca_perdic]

Hi Stevejoice,

I see.. no shorcuts.

Thank you for the answer