topic Re: sum in script in QlikView

sum in script

rcorcoran — Wed, 04 Jun 2014 10:46:45 GMT

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

[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

and

slmkey

142

and

MetricKey=3)

(tlmkey

and

slmkey

142

and

MetricKey=5)

(tlmkey

and

slmkey

166

and

MetricKey=4)

(tlmkey

and

slmkey=200

and

MetricKey=46)

(tlmkey=18

and

slmkey=200

and

MetricKey=47)

and

(ServiceProviderType='H'

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

Re: sum in script

tresB — Wed, 04 Jun 2014 10:50:18 GMT

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

Re: sum in script

rcorcoran — Wed, 04 Jun 2014 10:54:39 GMT

I have put it up now

Thanks

Re: sum in script

tresB — Wed, 04 Jun 2014 11:03:13 GMT

I guess you have to try like:

Load

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

From <> Group By;

Re: sum in script

its_anandrjs — Wed, 04 Jun 2014 11:13:09 GMT

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

Re: sum in script

rcorcoran — Wed, 04 Jun 2014 11:35:01 GMT

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

Re: sum in script

its_anandrjs — Wed, 04 Jun 2014 11:47:38 GMT

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.