Aggregate SUM - invalid expression

[Anonymous]

Hi All,

Please help me with Aggregate SUM , i am getting invalid expression when using below chart expression in script:

count({<[VALID] ={100},[IDETAILS KEY]-={'-1'}>}[IDETAILS KEY])/Count({<[IDETAILS KEY]-={'-1'}>}[IDETAILS KEY])

Script:

RIndDetails:

LOAD

  RPID_KEY as [IDETAILS KEY],

  RRCN_KEY ,

  RR_KEY as RR_KEY,

  PCDR_KEY as [PCDR KEY],     

  LCDR_KEY as [LCDR KEY],           

  RPCDR_KEY as [RPCDR KEY] ,  

  SCDR_KEY as [SCDR_KEY] ,      

   RCOMPLETE as [COMPLETE] ,

  RVALID as [VALID] ,

  RACCURACY as [ACCURATE] ,

  RMESSAGE as [IMESSAGE],     

  '1' as [R DETAILS IDENTIFIER],

  if (RPID_KEY = '-1',0,1) as  [IndDet Count]  

From $(vQVDDataPath)RPID.qvd(qvd);

Concatenate

LOAD

  RPIN_KEY as [IDETAILS KEY],

  RRCN_KEY ,

  RR_KEY as RR_KEY,

  PCDR_KEY as [PCDR KEY],     

  LCDR_KEY as [LCDR KEY],           

  RPCDR_KEY as [RPCDR KEY] ,  

  SCDR_KEY as [SCDR_KEY] ,      

   RCOMPLETE as [COMPLETE] ,

  RVALID as [VALID] ,

  RACCURACY as [ACCURATE] ,

  RMESSAGE as [IMESSAGE],

  '2' as [R DETAILS IDENTIFIER],

  if (RPIN_KEY = '-1',0,1) as  [IndDet Count]

From $(vQVDDataPath)RPIN.qvd(qvd);

Concatenate

LOAD

RPIT_KEY as [IDETAILS KEY],

  RRCN_KEY ,

  RR_KEY as RR_KEY,

  PCDR_KEY as [PCDR KEY],     

  LCDR_KEY as [LCDR KEY],           

  RPCDR_KEY as [RPCDR KEY] ,  

  SCDR_KEY as [SCDR_KEY] ,      

  RCOMPLETE as [COMPLETE] ,

  RVALID as [VALID] ,

  RACCURACY as [ACCURATE] ,

  RMESSAGE as [IMESSAGE],

  '3' as [R DETAILS IDENTIFIER],

  if (RPIT_KEY = '-1',0,1) as  [IndDet Count]

From $(vQVDDataPath)RPIT.qvd(qvd);

RDetails:

LOAD *,

sum(if([IndDet Count] = 1,[IDETAILS KEY])) as IC,

sum(if([VALID] = '100' , [IndDet Count] = '1',[IDETAILS KEY])) as ValidCount,

sum(if([COMPLETE]= '100',[IndDet Count] = '1',[IDETAILS KEY])) as CompleteCount,

sum(if([ACCURATE] = '100',[IndDet Count] = '1',[IDETAILS KEY])) as AccurateCount,

sum(if([VALID]= '0',[IndDet Count] = '1',[IDETAILS KEY])) as InValidCount,

sum(if([COMPLETE]= '0',[IndDet Count] = '1',[IDETAILS KEY])) as InCompleteCount,

sum(if([ACCURATE]= '0',[IndDet Count] = '1',[IDETAILS KEY])) as InAccurateCount

Resident RIndDetails ;

DROP Table RIndDetails;

[Anonymous]

Still issue persists

[agomes1971]

Please see this post: How to use if-statement in Qlikview?

Kind Regards

André Gomes

[#4 · Accepted solution]

I am not sure why you sum the values on the [IDETAILS KEY] and again using the same field in the Load as well. Please make sure that only Load fields must be present in Group by list .

Try like below:

RDetails:

LOAD

  [IDETAILS KEY],

  RRCN_KEY ,

  RR_KEY,

  [PCDR KEY],

  [LCDR KEY],

  [RPCDR KEY] ,

  [SCDR_KEY] ,

  [COMPLETE] ,

  [VALID] ,

  [ACCURATE] ,

  [IMESSAGE],

  [R DETAILS IDENTIFIER] ,

  sum( if([IndDet Count] = 1,[IDETAILS KEY])) as IC,

  sum(if([VALID] = '100' AND [INDVIVIDUAL COUNT] = '1',[IDETAILS KEY],0)) as ValidCount,

  sum(if([COMPLETE]= '100' AND [INDVIVIDUAL COUNT] = '1',[IDETAILS KEY],0)) as CompleteCount,

  sum(if([ACCURATE] = '100' AND [INDVIVIDUAL COUNT] = '1',[IDETAILS KEY],0)) as AccurateCount,

  sum(if([VALID]= '0' AND [INDVIVIDUAL COUNT] = '1',[IDETAILS KEY],0)) as InValidCount,

  sum(if([COMPLETE]= '0' AND [INDVIVIDUAL COUNT] = '1',[IDETAILS KEY],0)) as InCompleteCount,

  sum(if([ACCURATE]= '0' AND [INDVIVIDUAL COUNT] = '1',[IDETAILS KEY],0)) as InAccurateCount

Resident RIndDetails

group by

  RRCN_KEY ,

  RR_KEY,

  [PCDR KEY],

  [LCDR KEY],

  [RPCDR KEY] ,

  [SCDR_KEY] ,

  [COMPLETE] ,

  [VALID] ,

  [ACCURATE] ,

  [IMESSAGE],

  [R DETAILS IDENTIFIER] ,

  [IDETAILS KEY]

;

[Anonymous]

Hi Dathu,

Thanks,Its working , no error