Sum([Units]) AS "YTD Units" is giving syntax error!!

[srini]

Hi Experts,

when I use this in my load scrip it shows the syntax error

Sum([Units]) AS "YTD Units"

not sure? getting confused

[sunny_talwar]

Are you using Group by statement in the load where you are doing the Sum(Units) as "YTD Units"

[srini]

Hi Sunny,

Nope I am not using Group by statement.

[srini]

I have tried with group by statement but still shows the same error Test2: Load "Cycle Year", "Cycle Name", "Governance Group", "Product Line", "GPDM Portfolio", "GPDM Programme", "POB ID", "POB Name", "BP Priority", "BP ID (for filtering)", "BP Name", If( [Cycle Name] ='ACTUAL' AND MonthNum<=Num(Month(Today())), Units) AS "YTD Actuals", If( [Cycle Name] ='FORECAST' AND MonthNum<=Num(Month(Today())), Units) AS "YTD FORECAST", Sum([Units]) AS "YTD Units" Resident Test1 Group BY [Units],[Cycle Year],[Cycle Name],[BP ID (for filtering)]; Drop Table Test1;

[jonathandienst]

Every item not in an aggregation (Sum / Max / Avg etc) must be included in the Group By.

Test2: 
Load 
	"Cycle Year", 
	"Cycle Name", 
	"Governance Group", 
	"Product Line", 
	"GPDM Portfolio", 
	"GPDM Programme", 
	"POB ID", 
	"POB Name", 
	"BP Priority", 
	"BP ID (for filtering)", 
	"BP Name", 
	If( [Cycle Name] ='ACTUAL' AND MonthNum<=Num(Month(Today())), Units) AS "YTD Actuals", 
	If( [Cycle Name] ='FORECAST' AND MonthNum<=Num(Month(Today())), Units) AS "YTD FORECAST", 
	Sum([Units]) AS "YTD Units" 
Resident Test1 
Group BY "Cycle Year", 
	"Cycle Name", 
	"Governance Group", 
	"Product Line", 
	"GPDM Portfolio", 
	"GPDM Programme", 
	"POB ID", 
	"POB Name", 
	"BP Priority", 
	"BP ID (for filtering)", 
	"BP Name", 
	If( [Cycle Name] ='ACTUAL' AND MonthNum<=Num(Month(Today())), Units),
	If( [Cycle Name] ='FORECAST' AND MonthNum<=Num(Month(Today())), Units)
;

Drop Table Test1;

[srini]

Thanks Jonty!