Expression Invalid - if Sum of fields is less than....

[#1]

Hi everyone,

Would someone be able to tell me what is wrong with the below formula? I am getting 'expression invalid' when I run the script.

I have two fields, the first looks at if AVPUPatUncon is a cetain value and returns a 1, the second field is a sum of the GCS to determine if the value is less than 15. When I combine these two fields into the one statement I receive the error.

IF((SUM(Observations.GCSEyes)+SUM(Observations.GCSVerbal)+SUM(Observations.GCSMotor)<15) and Observations.AVPUPatUncon=1,1,0) as Observations.AsthmaPatUnconFlag

The full script that is below.

LOAD

            %KEY_PRFUID,

            SUM(Observations.GCSEyes)+SUM(Observations.GCSVerbal)+SUM(Observations.GCSMotor) as Observations.GCSScore,

            IF(SUM(Observations.GCSEyes)+SUM(Observations.GCSVerbal)+SUM(Observations.GCSMotor)<15,1,0) as Observations.GCSLT15,

            IF((SUM(Observations.GCSEyes)+SUM(Observations.GCSVerbal)+SUM(Observations.GCSMotor)<15) and Observations.AVPUPatUncon=1,1,0) as Observations.AsthmaPatUnconFlag

//          IF(Observations.AVPUPatUncon=1 and IF(SUM(Observations.GCSEyes)+SUM(Observations.GCSVerbal)+SUM(Observations.GCSMotor)<15,1),1,0) as Observations.AsthmaPatUnconFlag

Resident Observations

GROUP BY %KEY_PRFUID;

Any help would be really appreciated.

[#2]

Hi Phil,

Is it possible for you to upload a sample qvw file with Observations table with some records?

Thanks,

Sajeevan

[#3]

Hi, here is the qvw with a table of the fields I am using. Hopefully this will help.

[#4]

try this:

LOAD

%KEY_PRFUID,
Observations.ObsSeqNo,
Observations.AVPU,
Observations.AVPUPatUncon,
Observations.GCSEyes,
Observations.GCSVerbal,
Observations.GCSMotor,
Observations.GCSEyes+Observations.GCSVerbal+Observations.GCSMotor as Observations.GCSScore,
IF(Observations.GCSEyes+Observations.GCSVerbal+Observations.GCSMotor<15,1,0) as Observations.GCSLT15,
IF(Observations.GCSEyes+Observations.GCSVerbal+Observations.GCSMotor<15 and Observations.AVPUPatUncon=1,1,0) as Observations.AsthmaPatUnconFlag
// IF(Observations.AVPUPatUncon=1 and IF(SUM(Observations.GCSEyes)+SUM(Observations.GCSVerbal)+SUM(Observations.GCSMotor)<15,1),1,0) as Observations.AsthmaPatUnconFlag

FROM

[#5]

Hi Phil,

Since you are using group by you need to use the sum function on Observations.AVPUPatUncon.

if you use the below, it loads correctly.

IF((SUM(Observations.GCSEyes)+SUM(Observations.GCSVerbal)+SUM(Observations.GCSMotor) < 15) and (sum(Observations.AVPUPatUncon)=1),1,0) as Observations.AsthmaPatUnconFlag

I have done it by extracting the data to an excel file and then reloaded like below

Data1:
LOAD %KEY_PRFUID,
Observations.ObsSeqNo,
Observations.AVPU,
Observations.AVPUPatUncon,
Observations.RespRate,
Observations.RespRateFlag,
Observations.SPO2,
Observations.SPO2Flag,
Observations.PeakFlow,
Observations.PeakFlowFlag,
Observations.PeakFlowRefusedFlag,
Observations.PeakFlowUnableFlag,
Observations.BPSyst,
Observations.BPDias,
Observations.GCSEyes,
Observations.GCSVerbal,
Observations.GCSMotor,
Observations.iPainScore,
Observations.bm
FROM
C:\Sajeevan\QlikView\2011\Community\Data_Test.xls
(biff, embedded labels, table is Sheet1$);

Data2:
LOAD %KEY_PRFUID,
SUM(Observations.GCSEyes)+SUM(Observations.GCSVerbal)+SUM(Observations.GCSMotor) as Observations.GCSScore
,IF(SUM(Observations.GCSEyes)+SUM(Observations.GCSVerbal)+SUM(Observations.GCSMotor)<15,1,0) as Observations.GCSLT15
,IF((SUM(Observations.GCSEyes)+SUM(Observations.GCSVerbal)+SUM(Observations.GCSMotor) < 15) and (sum(Observations.AVPUPatUncon)=1),1,0) as Observations.AsthmaPatUnconFlag
//     ,IF(Observations.AVPUPatUncon=1 and IF(SUM(Observations.GCSEyes)+SUM(Observations.GCSVerbal)+SUM(Observations.GCSMotor)<15,1),1,0) as Observations.AsthmaPatUnconFlag
Resident Data1
GROUP BY %KEY_PRFUID;

[#6]

Hi  Sajeevan,

Thanks for the reply, its helped over come the error message. I need to revisit the logic as tis not giving the values I was expecting.

Thanks again.