Sum in Script

[etrotter]

Hello,

I am having trouble with this expression:

Temp_Sum:

Load

[%CSLGranteeExternalID],

[CSL Account Name],

Sum(Num([CSL Total Annual Revenue])) as [CSL Sum Total Revenue]

Resident Account

Group By [%CSLGranteeExternalID],[CSL Account Name];

It will not load, please let me know what you think could be the problem. I keep getting the error "Invalid Expression" when I run the script. 

[Chanty4u]

did you tried without num and reload?

[Chanty4u]

try

Temp_Sum:

NoConcatenate

Load

[%CSLGranteeExternalID],

[CSL Account Name],

Sum([CSL Total Annual Revenue]) as [CSL Sum Total Revenue]

Resident Account

Group By [%CSLGranteeExternalID],[CSL Account Name];

[etrotter]

I tried both and still recieving the same error message.

[Chanty4u]

can you paste your full script ?

[balabhaskarqlik]

May be can you try like this:

Account:

Load

[%CSLGranteeExternalID],

[CSL Account Name],

[CSL Total Annual Revenue],

...

FROM ABC;

NoConcatenate

Temp_Sum:

Load

[%CSLGranteeExternalID],

[CSL Account Name],

Num(Sum([CSL Total Annual Revenue]),'##,#.00') as [CSL Sum Total Revenue]

Resident Account

Group By [%CSLGranteeExternalID];

Drop table Account;

You could check all the field names of Temp_Sum, matched correctly with Account table.

Or else, could you provide some more script to identify the issue.

[etrotter]

Hi, I actually ended up changing the script quite a bit, here is the new script I'm working on, but still haven't been able to get it working:

AllOrgs:

Load

"Grant ID",

%CSLGrantorExternalID,

[CSL Account ID],

[%CSLGranteeExternalID],

//[CSL Account Name],

if([CSL Rev by Funder]>20000,[CSL Account Name], 'All Other Grantees') as [CSL Account Name],

[CSL Account Type],

[CSL Zip],

[CSL Website],

[CSL Total Annual Revenue],

[CSL Total Annual Regranting],

[CSL Org Mission],

[CSL Year of Data],

[CSL No of Employees],

[RCF as Funder];

Load *,

Sum([CSL Total Annual Revenue]) as [CSL Rev by Funder]

Resident AllOrgs_TempGrantee

Group by %CSLGrantorExternalID;

Drop table AllOrgs_TempGrantee;

[balabhaskarqlik]

Resident AllOrgs_TempGrantee

Where is this: AllOrgs_TempGrantee table?

May be you can try like this:

AllOrgs:

Load

"Grant ID",

%CSLGrantorExternalID,

[CSL Account ID],

[%CSLGranteeExternalID],

//[CSL Account Name],

if([CSL Rev by Funder]>20000,[CSL Account Name], 'All Other Grantees') as [CSL Account Name],

[CSL Account Type],

[CSL Zip],

[CSL Website],

[CSL Total Annual Revenue],

[CSL Total Annual Regranting],

[CSL Org Mission],

[CSL Year of Data],

[CSL No of Employees],

[RCF] as Funder;

NoConcatenate

AllOrgs_TempGrantee

Load *,

Sum([CSL Total Annual Revenue]) as [CSL Rev by Funder]

Resident AllOrgs

Group by %CSLGrantorExternalID,[CSL Account Type];

Drop table AllOrgs;

[mhappiee]

Hi Erica,

Try this 3 and see if that helps:

-try adding all fields in the group by except the aggregating field and then do a resident load of that in a different table

- Try converting the format of the measure in 1st table check in UI and then try aggregate it by resident load .

-try other aggregate functions with 1 field like max and continue adding other fields if it works then you can replace it by Sum

Anand