Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I'm trying to sum a budget number as it comes out in numerous rows and I need it all grouped so it can be added to another table.
I've been using sum but this doesn't seem to work.
Below is the script i'm trying but it keeps telling me it is invalid, could someone please help?
TEMP:
SQL SELECT
AccountLevel3,
ActivityLevel1,
ActivityLevel2,
ActivityLevel3,
ActivityLevel4,
ActivityLevel5,
amount,
"amount_variant",
client,
CostCenterLevel2,
"period_month",
"period_year"
FROM TEST
WHERE CostCenterLevel2 <> '111' AND CostCenterLevel2 <> '112'
AND "period_year" >= '2016'
AND AccountLevel3 <> '9000' AND AccountLevel3 <> '9200' AND AccountLevel3 <> '9500'
AND "amount_variant" = 'BR';
Budget:
Load
AccountLevel3 as Accountlvl2,
CostCenterLevel2 as IE_Unit,
amount,
SUM(amount) as Budget,
"period_month",
"period_year",
"period_month" & "period_year" as FY_LUP
Resident TEMP
Group By
AccountLevel3, CostCenterLevel2
;
Drop Table TEMP;
If this is really your script you're missing a few things. Something like this would be closer.
TEMP:
load *
;
SQL SELECT
AccountLevel3,
ActivityLevel1,
ActivityLevel2,
ActivityLevel3,
ActivityLevel4,
ActivityLevel5,
amount,
"amount_variant",
client,
CostCenterLevel2,
"period_month",
"period_year"
FROM TEST
WHERE CostCenterLevel2 <> '111' AND CostCenterLevel2 <> '112'
AND "period_year" >= '2016'
AND AccountLevel3 <> '9000' AND AccountLevel3 <> '9200' AND AccountLevel3 <> '9500'
AND "amount_variant" = 'BR';
Budget:
load *,
"period_month" & "period_year" as FY_LUP
;
Load
AccountLevel3 as Accountlvl2,
CostCenterLevel2 as IE_Unit,
SUM(amount) as Budget,
"period_month",
"period_year",
Resident TEMP
Group By
AccountLevel3, CostCenterLevel2,period_month,period_year
;
Drop Table TEMP;
Hi,
Please try like this below.
I have created dummy application to explain your problem
Sample code: Sample Data
RESULT may look like
when you are using sum(fact) in your script it should group by with all its dimension value. Also you can use it in preceding load function please refer below thread for more information
IF and SUM in script load from SQL
Note: if you are adding more than 1 dimension values in your script you need to include it in your group by function