How to avoid error in expected expression ')' with below calculation
Hi all,
I've joined two tables in my script to get the table I require. I'd like to use a resident load to calculate a field after the join.
In the attached sheet you can see the results of the join gives me a row of data for each week in the year per budget line. The budget amount is the full budget for the month populated against each row.
I am trying to recalculate the weekly budget by taking the budget amount for each budget line in each month and dividing it by the count of "Month Name" in each Month.
So, January has 6 weeks, therefore Jan 2021 populates in 6 rows
So the weekly budget amount for "Quality of life" should be £400/6
The full script looks like this:
Unqualify *;
[Sheet1_temp_77962d53-c1fd-66e1-3014-7a562cb2]: LOAD [CDate] AS [Sheet1.CDate], [Budget Category], [Month Name], [Weeks in Month], [Budget amount], [Weekly Budget], [Daily Budget], [Budget Re-categorised] RESIDENT [Sheet1]; DROP TABLE [Sheet1];
LEFT JOIN ([Sheet1_temp_77962d53-c1fd-66e1-3014-7a562cb2]) LOAD [CDate] AS [Sheet1.CDate], [Month], [Week], [Current week], [Number of Days], [nbWeek] RESIDENT [hConcatenate]; DROP TABLE [hConcatenate];
RENAME TABLE [Sheet1_temp_77962d53-c1fd-66e1-3014-7a562cb2] to [Sheet1];
This is where I get the error "error in expected expression ')' " :
Result:
Load *,
Sum(Distinct([Budget amount]))/Count(total <[Month]> [MonthName]) AS [Weekly Budget 3]
RESIDENT [WeeklyBudget2];
STORE WeeklyBudget2 into [$(vS2QVDFolder)/WeeklyBudget2.csv] (txt); DROP TABLE Sheet1;
Please help, how do I get the above script, or something that does the same job to run without the error?
