Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Could you post the entire load statement (for this one only) you are trying with?
Hi
I have put it up now
Thanks
I guess you have to try like:
Load
Sum(If(......)) as YourNewFieldName
From <> Group By;
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
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
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.