topic Re: sum in load in QlikView

sum in load

rcorcoran — Wed, 09 Jul 2014 08:45:43 GMT

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

];

Re: sum in load

Wed, 09 Jul 2014 08:52:49 GMT

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;

Re: sum in load

rcorcoran — Wed, 09 Jul 2014 09:00:11 GMT

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>

Re: sum in load

Wed, 09 Jul 2014 09:10:36 GMT

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

Re: sum in load

rcorcoran — Wed, 09 Jul 2014 09:14:00 GMT

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>

Re: sum in load

Wed, 09 Jul 2014 09:22:36 GMT

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;

Re: sum in load

rcorcoran — Wed, 09 Jul 2014 09:39:48 GMT

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>

Re: Re: sum in load

Wed, 09 Jul 2014 10:03:39 GMT

Rhona ,

Try to implement like mentioned in attached qvw...

Hopeit works..

PFA

Re: sum in load

rcorcoran — Wed, 09 Jul 2014 10:09:12 GMT

Thanks will try that and wont annoy you again