REally sorry but I can't get this sum to work in the script - see below where I am trying to sum Metrics 18.205.3 and 18.205.4 as Outpatients in a resident table and grouping by TLMKey, SLMKey and Metric Key. While it loads it does not add the metrics as required.
If anyone had a minute to look at it it would really belp me
OLEDBCONNECT32TO[Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Healthstat;Data Source=cifdevelopment;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=EL6470B0001;Use Encryption for Data=False;Tag with column collation when possible=False];
LEFTJOIN (EWTD) LOAD TLMKey, SLMKey, MetricKey, //sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5)) ,ActualValue)) as Outpatients sum(if((TLMKey=18 andSLMKey=205) and ((MetricKey=3) and (MetricKey=4)) ,ActualValue)) asOutpatients //sum(if((TLMKey=18 and SLMKey=142 and MetricKey=3) and (TLMKey=18 and SLMKey=142 and MetricKey=5) and (TLMKey=18 and SLMKey=142 and MetricKey=4))ActualValue as Outpatients
By definition if you need to perform any aggregation calculation within script (Sum, Max, Min, Avg, etc) you need to include a group by in your table with all the involved fields (In some way it's like using aggr at script level):