Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I still havent got very far with this. so here is the full script. You can see where I am trying to sum Metric Key 3,4 and 5 where the TLMKey is 18 and the SLMKey is 142. I know you have to group by somehow but not sure how. If anyone had a minute could they help please. Thanks
EWTD:
LOAD ActualValue,
TLMKey,
SLMKey,
MetricDesc,
MetricKey,
//Acute hospital metrics
if((TLMKey=18 and SLMKey=142 and MetricKey=3) or (TLMKey=18 and SLMKey=142 and MetricKey=5) or (TLMKey=18 and SLMKey=142 and MetricKey=4),MetricKey) as Discharges,
sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5)) ,ActualValue)) as Outpatients,
if(TLMKey=18 and SLMKey=166 and MetricKey=4,MetricKey) as [Reducing Health Care acquired Infection],
if((TLMKey = 18 and SLMKey = 200 and MetricKey=4) or (TLMKey = 18 and SLMKey = 200 and MetricKey=46),MetricKey) as [Rates of Readmission],
//Primary Care Metrics
if(TLMKey = 10 and SLMKey = 103 and (MetricKey>=1 or MetricKey <=4),MetricKey) as [Community Intervention Team by Source],
if(TLMKey=10 and SLMKey=192 and MetricKey=20,MetricKey) as [Physio Wait >12 wks],
if(TLMKey = 10 and SLMKey = 191 and (MetricKey>=68 or MetricKey <=79),MetricKey) as [OccTherapy wait> 16 weeks],
if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10 and SLMKey=101 and MetricKey=2) or (TLMKey=10 and SLMKey=101 and MetricKey=11) or(TLMKey=18 and SLMKey=142 and MetricKey=12),MetricKey) as [Ortho No Wait 12 Mths],
if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10 and SLMKey=101 and MetricKey=2),MetricKey) as [Ortho Wait >4 yrs],
if((TLMKey=10 and SLMKey = 101 and (MetricKey>=1 or MetricKey <=4) or (TLMKey=10 and SLMKey = 101 and (MetricKey>=5 or MetricKey <=16))),MetricKey) as Orthodontics,
RDODesc,
RDOKey,
ReferredMM,
ReferredYYYY,
SGKey,
SGName,
ServiceProvider,
ServiceProviderType,
ServiceProviderNum,
if(ServiceProviderType='H',ServiceProvider) as Hospital,
if(ServiceProviderType='T',ServiceProvider) as HospitalGroup,
if(ServiceProviderType='O',ServiceProvider) as Team
;
SQL SELECT *
FROM Healthstat.dbo."MMI_ScorecardLevel3"
where (tlmkey = 18 and slmkey = 142 and MetricKey=3) or (tlmkey = 18 and slmkey = 142 and MetricKey=4)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 = 10 and slmkey = 192 and MetricKey=20 )or (tlmkey = 18 and slmkey = 200 and MetricKey=4)or
(tlmkey = 10 and slmkey = 103 and (MetricKey>=1 or MetricKey <=4))or (tlmkey = 10 and slmkey = 191 and (MetricKey>=68 or MetricKey <=79))or (tlmkey = 10 and slmkey = 101 and (MetricKey>=1 or MetricKey <=2)) or (tlmkey = 10 and slmkey = 101 and (MetricKey>=11 or MetricKey <=12)) or (tlmkey = 10 and slmkey = 101 and MetricKey=10) or (tlmkey = 10 and slmkey = 101 and MetricKey=16)
or (tlmkey=10 and slmkey = 101 and (MetricKey>=1 or MetricKey <=4)) or (tlmkey=10 and slmkey = 101 and (MetricKey>=5 or MetricKey<=16))
and (ServiceProviderType='H' or ServiceProviderType='T' or ServiceProviderType='O');
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
];
Try this:
EWTD:
LOAD ActualValue,
TLMKey,
SLMKey,
MetricDesc,
MetricKey,
sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5)) ,ActualValue)) as Outpatients
from SourcePath Group By TLMKey,SLMKey,MetricKey;
Thanks Harshal
Where do I put this do I put it after my full load or do you mean to put
in the full EWTD Table and then group by at the end
Sorry Confused
Rhona
Rhona Corcoran
ICT Services
Health Service Executive, Dr. Steeven's Hospital, Dublin 8.
Tel: 01 6352710 (am only) ;
Email: rhona.corcoran@hse.ie <mailto:rhona.corcoran@hse.ie>
You are using aggr function in script level so u need to mention group by at the end of table in which you are trying to use sum function.
Put it at the end of EWTD table after Sourcepath...
Thanks for your help will try that
Rhona Corcoran
ICT Services
Health Service Executive, Dr. Steeven's Hospital, Dublin 8.
Tel: 01 6352710 (am only) ;
Email: rhona.corcoran@hse.ie <mailto:rhona.corcoran@hse.ie>
Rhona ,
You can Load full EWTD table first without performing sum comment that after getting data for EWTD table ,
take resident load of EWTD table with new table and apply logic over their...
NewTbl:
TLMKey,
SLMKey,
MetricKey,
sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5)) ,ActualValue)) as Outpatients
resident EWTD Group By TLMKey,SLMKey,MetricKey;
It will result some synthetic key..If possible do follow below approach..
EWTD:
Load
...
sql load where ...;
store EWTD into EWTD.qvd;
drop table EWTD;
Now Load data from EWTD.qvd
NewEWTD:
Load from Your EWTD.qvd...with adding TLMKey &' '& SLMKey &' '& MetricKey as Key field
NewEWTD_1:
Load
TLMKey &' '& SLMKey &' '& MetricKey as Key,
sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5)) ,ActualValue)) as Outpatients
resident EWTD Group By TLMKey,SLMKey,MetricKey;
Sorry to take up all your time but this is what I have in now and it is
saying invalid expression. When I reload the data:
EWTD:
LOAD ActualValue,
TLMKey,
SLMKey,
MetricDesc,
MetricKey,
//Acute hospital metrics
if((TLMKey=18 and SLMKey=142 and MetricKey=3) or (TLMKey=18 and
SLMKey=142 and MetricKey=5) or (TLMKey=18 and SLMKey=142 and
MetricKey=4),MetricKey) as Discharges,
if(TLMKey=18 and SLMKey=166 and MetricKey=4,MetricKey) as
,
if((TLMKey = 18 and SLMKey = 200 and MetricKey=4) or (TLMKey =
18 and SLMKey = 200 and MetricKey=46),MetricKey) as [Rates of
Readmission],
//Primary Care Metrics
if(TLMKey = 10 and SLMKey = 103 and (MetricKey>=1 or MetricKey
<=4),MetricKey) as ,
if(TLMKey=10 and SLMKey=192 and MetricKey=20,MetricKey) as
,
if(TLMKey = 10 and SLMKey = 191 and (MetricKey>=68 or
MetricKey <=79),MetricKey) as ,
if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10
and SLMKey=101 and MetricKey=2) or (TLMKey=10 and SLMKey=101 and
MetricKey=11) or(TLMKey=18 and SLMKey=142 and MetricKey=12),MetricKey)
as ,
if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10
and SLMKey=101 and MetricKey=2),MetricKey) as ,
if((TLMKey=10 and SLMKey = 101 and (MetricKey>=1 or MetricKey
<=4) or (TLMKey=10 and SLMKey = 101 and (MetricKey>=5 or MetricKey
<=16))),MetricKey) as Orthodontics,
RDODesc,
RDOKey,
ReferredMM,
ReferredYYYY,
SGKey,
SGName,
ServiceProvider,
ServiceProviderType,
ServiceProviderNum,
if(ServiceProviderType='H',ServiceProvider) as Hospital,
if(ServiceProviderType='T',ServiceProvider) as HospitalGroup,
if(ServiceProviderType='O',ServiceProvider) as Team
;
SQL SELECT *
FROM Healthstat.dbo."MMI_ScorecardLevel3"
where (tlmkey = 18 and slmkey = 142 and MetricKey=3) or (tlmkey = 18 and
slmkey = 142 and MetricKey=4)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 = 10 and slmkey =
192 and MetricKey=20 )or (tlmkey = 18 and slmkey = 200 and
MetricKey=4)or
(tlmkey = 10 and slmkey = 103 and (MetricKey>=1 or MetricKey <=4))or
(tlmkey = 10 and slmkey = 191 and (MetricKey>=68 or MetricKey <=79))or
(tlmkey = 10 and slmkey = 101 and (MetricKey>=1 or MetricKey <=2)) or
(tlmkey = 10 and slmkey = 101 and (MetricKey>=11 or MetricKey <=12)) or
(tlmkey = 10 and slmkey = 101 and MetricKey=10) or (tlmkey = 10 and
slmkey = 101 and MetricKey=16)
or (tlmkey=10 and slmkey = 101 and (MetricKey>=1 or MetricKey <=4)) or
(tlmkey=10 and slmkey = 101 and (MetricKey>=5 or MetricKey<=16))
and (ServiceProviderType='H' or ServiceProviderType='T' or
ServiceProviderType='O')
;
Load
ActualValue,
TLMKey,
SLMKey,
MetricDesc,
MetricKey,
sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5))
,ActualValue)) as Outpatients
resident EWTD Group By TLMKey,SLMKey,MetricKey;
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
];
Rhona Corcoran
ICT Services
Health Service Executive, Dr. Steeven's Hospital, Dublin 8.
Tel: 01 6352710 (am only) ;
Email: rhona.corcoran@hse.ie <mailto:rhona.corcoran@hse.ie>
Rhona ,
Try to implement like mentioned in attached qvw...
Hopeit works..
PFA
Thanks will try that and wont annoy you again