QV Script Sum problem

[aliyoung92]

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;

[Anonymous]

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;

[Anonymous]

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