need help on resident code.

[#1]

Hi, i am getting script error for below code:

BudgetTemp:
CROSSTABLE(State1,BUDGETREVENUE,4)
LOAD TYPE,
VENDOR,
'FY' & right([FY YEAR],2) as [FY YEAR1],
[FY MONTH],
NSW,
VIC,
QLD,
SA,
WA,
ACT,
TAS,
NT,
NZ,
OTHER
FROM
avnet_budget_data.txt
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Budget:
LOAD rowno() as BudgetID,
[FY MONTH] as [Fiscal Month],
[FY YEAR1],
TYPE,
VENDOR,
State1,
SUM(if(TYPE = 'budget',BUDGETREVENUE)) as BUDGET,
SUM(if(TYPE = 'forecast',BUDGETREVENUE)) as FORECAST
RESIDENT BudgetTemp;

Could you please help me out with this syntax error?

[jagan · Accepted solution]

Hi,

I think you are getting error in second Load statement, because you are using an aggregation function(Sum) without using Group By.

Try this

Budget:
LOAD rowno() as BudgetID,
[FY MONTH] as [Fiscal Month],
[FY YEAR1],
TYPE,
VENDOR,
State1,
SUM(if(TYPE = 'budget',BUDGETREVENUE)) as BUDGET,
SUM(if(TYPE = 'forecast',BUDGETREVENUE)) as FORECAST
RESIDENT BudgetTemp

GROUP BY [FY MONTH][FY YEAR1],
TYPE,
VENDOR,
State1;

Hope this helps you.

Regards,

Jagan.

[#3]

Hi Jagan,yes, you are right.thanks for your help.