sum in script

[rcorcoran]

Ok

So I am trying to add the values together of the two metrics below in the script so that I can then use this value in other calculations in a table and I am not having much luck.  Can anyone help?

sum

(TLMKey=18 and SLMKey=200 and MetricKey=46) and (TLMKey=18 and SLMKey=200 and MetricKey=47),ActualValue as

[Surgical Admissions]

Full load statement:

SET

ThousandSep

=',';
SET

DecimalSep

='.';
SET

MoneyThousandSep

=',';
SET

MoneyDecimalSep

='.';
SET

MoneyFormat

='€#,##0.00;-€#,##0.00';
SET

TimeFormat

='hh:mm:ss';
SET

DateFormat

='DD/MM/YYYY';
SET

TimestampFormat

='DD/MM/YYYY hh:mm:ss[.fff]';
SET

MonthNames

='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET

DayNames

='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB

CONNECT32

TO

[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]

;


//-------- Start Multiple Select Statements ------

EWTD:
LOAD

ActualValue

,
TLMKey

,
SLMKey

,
MetricDesc

,
MetricKey

,
if

((TLMKey

=18 and

SLMKey

=142 and

MetricKey

=3) or

(TLMKey

=18 and

SLMKey

=142 and

MetricKey

=5),MetricKey

) as

Discharges

,
if

(TLMKey

=18 and

SLMKey

=166 and

MetricKey

=4,MetricKey

) as

[Reducing Health Care acquired Infection]

,
if

((TLMKey

=18 and

SLMKey

=200 and

MetricKey

=46) or

(TLMKey

=18 and

SLMKey

=200 and

MetricKey

=47),MetricKey

) as

[Surgical Admissions]

,
RDODesc

,
RDOKey

,
ReferredMM

,
ReferredYYYY

,
SGKey

,
SGName

,
ServiceProvider

,
ServiceProviderType

,
ServiceProviderNum

,
if

(ServiceProviderType

='H',ServiceProvider

) as

Hospital

,
if

(ServiceProviderType

='T',ServiceProvider

) as

HospitalGroup

;


SQL

SELECT

*

FROM

Healthstat.dbo."MMI_ScorecardLevel3"

where

(tlmkey

=

18

and

slmkey

=

142

and

MetricKey=3)

or

(tlmkey

=

18

and

slmkey

=

142

and

MetricKey=5)

or

(tlmkey

=

18

and

slmkey

=

166

and

MetricKey=4)

or

(tlmkey

=

18

and

slmkey=200

and

MetricKey=46)

or

(tlmkey=18

and

slmkey=200

and

MetricKey=47)

and

(ServiceProviderType='H'

or

ServiceProviderType='T')

;


MonthMapping:

LOAD

* INLINE

[
ReferredMM, Month

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, June

7, July

8, Aug

9, Sept

10, Oct

11, Nov

12, Dec

]

;

Thanks for all the help so far this is a great forum

Rhona

[tresesco]

Could you post the entire load statement (for this one only) you are trying with?

[rcorcoran]

Hi

I have put it up now

Thanks

[tresesco]

I guess you have to try like:

Load

          Sum(If(......)) as YourNewFieldName

From <> Group By;

[its_anandrjs]

Update now check sorry i forget to add drop statement

Take resident table and load your table like below script

tmpEWTD:

LOAD

ActualValue,

TLMKey,

SLMKey,

MetricDesc,

MetricKey,

if((TLMKey=18 and SLMKey=142 and MetricKey =3) or (TLMKey =18 and SLMKey =142 and MetricKey =5),MetricKey ) as Discharges,

if(TLMKey=18 and SLMKey =166 and MetricKey=4,MetricKey) as[Reducing Health Care acquired Infection],

if((TLMKey=18 and SLMKey=200 and MetricKey=46) or (TLMKey=18 and SLMKey=200 and MetricKey=47),MetricKey) as[Surgical Admissions],

RDODesc,

RDOKey,

ReferredMM,

ReferredYYYY,

SGKey,

SGName,

ServiceProvider,

ServiceProviderType,

ServiceProviderNum,

if(ServiceProviderType='H',ServiceProvider) as Hospital,

if(ServiceProviderType='T',ServiceProvider) as HospitalGroup;

SQL

SELECT

*

FROM

Healthstat.dbo."MMI_ScorecardLevel3"

where

(tlmkey=18 and slmkey=142 and MetricKey=3) or

(tlmkey=18 and slmkey=142 and MetricKey=5) or

(tlmkey=18 and slmkey=166 and MetricKey=4) or

(tlmkey=18 and slmkey=200 and MetricKey=46) or

(tlmkey=18 and slmkey=200 and MetricKey=47)

and

(ServiceProviderType='H' or ServiceProviderType='T');

Noconcatenate

EWTD:

Load

ActualValue,

TLMKey,

SLMKey,

MetricDesc,

MetricKey,

Discharges,

[Reducing Health Care acquired Infection],

[Surgical Admissions],

RDODesc,

RDOKey,

ReferredMM,

ReferredYYYY,

SGKey,

SGName,

ServiceProvider,

ServiceProviderType,

ServiceProviderNum,

Hospital,

HospitalGroup,

sum(if((TLMKey=18 and SLMKey=200 and Match(MetricKey,46,47)) ,ActualValue)) as [Surgical Admissions]

Resident tmpEWTD

Group By

ActualValue,

TLMKey,

SLMKey,

MetricDesc,

MetricKey,

Discharges,

[Reducing Health Care acquired Infection],

[Surgical Admissions],

RDODesc,

RDOKey,

ReferredMM,

ReferredYYYY,

SGKey,

SGName,

ServiceProvider,

ServiceProviderType,

ServiceProviderNum,

Hospital,

HospitalGroup;

Drop table tmpEWTD; //This is important

[rcorcoran]

Thanks anand I have tried this but the load is failing with the following error:

Syntax error, missing/misplaced FROM:

tmpEWTD:

I cant see where it is wrong

[its_anandrjs]

For your table load from SQL i renamed as

And then create the resident load of the table by Name EWTD and call tmpEWTD on that and do aggregation and make the SUM field named [Surgical Admissions]

tmpEWTD:///This are Important

LOAD

ActualValue,

TLMKey,

SLMKey,

MetricDesc,

MetricKey,

if((TLMKey=18 and SLMKey=142 and MetricKey =3) or (TLMKey =18 and SLMKey =142 and MetricKey =5),MetricKey ) as Discharges,

if(TLMKey=18 and SLMKey =166 and MetricKey=4,MetricKey) as[Reducing Health Care acquired Infection],

if((TLMKey=18 and SLMKey=200 and MetricKey=46) or (TLMKey=18 and SLMKey=200 and MetricKey=47),MetricKey) as[Surgical Admissions],

RDODesc,

RDOKey,

ReferredMM,

ReferredYYYY,

SGKey,

SGName,

ServiceProvider,

ServiceProviderType,

ServiceProviderNum,

if(ServiceProviderType='H',ServiceProvider) as Hospital,

if(ServiceProviderType='T',ServiceProvider) as HospitalGroup;

SQL

SELECT

*

FROM

Healthstat.dbo."MMI_ScorecardLevel3"

where

(tlmkey=18 and slmkey=142 and MetricKey=3) or

(tlmkey=18 and slmkey=142 and MetricKey=5) or

(tlmkey=18 and slmkey=166 and MetricKey=4) or

(tlmkey=18 and slmkey=200 and MetricKey=46) or

(tlmkey=18 and slmkey=200 and MetricKey=47)

and

(ServiceProviderType='H' or ServiceProviderType='T');

Noconcatenate /////This are Important

EWTD: ///This are Important

Load

ActualValue,

TLMKey,

SLMKey,

MetricDesc,

MetricKey,

Discharges,

[Reducing Health Care acquired Infection],

[Surgical Admissions],

RDODesc,

RDOKey,

ReferredMM,

ReferredYYYY,

SGKey,

SGName,

ServiceProvider,

ServiceProviderType,

ServiceProviderNum,

Hospital,

HospitalGroup,

sum(if((TLMKey=18 and SLMKey=200 and Match(MetricKey,46,47)) ,ActualValue)) as [Surgical Admissions] ///This are Important

Resident tmpEWTD ///This are Important

Group By ///This are Important

ActualValue,

TLMKey,

SLMKey,

MetricDesc,

MetricKey,

Discharges,

[Reducing Health Care acquired Infection],

[Surgical Admissions],

RDODesc,

RDOKey,

ReferredMM,

ReferredYYYY,

SGKey,

SGName,

ServiceProvider,

ServiceProviderType,

ServiceProviderNum,

Hospital,

HospitalGroup;

Drop table tmpEWTD; //This is important

Note:- Now check i update and use Noconcatenate also.