Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rcorcoran
Creator
Creator

sum in load

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

]
;

8 Replies
Not applicable

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;

rcorcoran
Creator
Creator
Author

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>

Not applicable

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...

rcorcoran
Creator
Creator
Author

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>

Not applicable

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;



rcorcoran
Creator
Creator
Author

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>

Not applicable

Rhona ,

Try to implement like mentioned in attached qvw...

Hopeit works..

PFA

rcorcoran
Creator
Creator
Author

Thanks will try that and wont annoy you again