Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

neetha_p
Honored Contributor

Aggregate SUM - invalid expression

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;

1 Solution

Accepted Solutions
Not applicable

Re: Aggregate SUM - invalid expression

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]

;

23 Replies
qlikoqlik
Contributor II

Re: Aggregate SUM - invalid expression

Hi Nitha

Set analysis syntax can not be used in the script, however you can get the same results though

Thanks and regards

Padma

Not applicable

Re: Aggregate SUM - invalid expression

Hi Nitha, Set analysis is only UI feature in Qlikview. So you can't use the SET Analysis in scripting.

If you are using Aggregation functions like SUM ,COUNT etc in script, you should have to use GROUP BY Clause.

LOAD,

     DIM1,

     DIM2,

     count(IF(VALID=100 AND [IDETAILS KEY] <>1, [IDETAILS KEY])/count(IF([IDETAILS KEY] <>1, [IDETAILS KEY])

FROM TableName

Group By

     DIM1, DIM2

neetha_p
Honored Contributor

Re: Aggregate SUM -  invalid expression

hi dathu,

thanks, please can you give info what fields to use in group by clause.

Because i am not using any dimensions in chart

regards

nitha

Not applicable

Re: Aggregate SUM - invalid expression

Do you need this calculation in Script or UI ?

neetha_p
Honored Contributor

Re: Aggregate SUM - invalid expression

Hi dathu,

in script please.

chauhans85
Esteemed Contributor

Re: Aggregate SUM - invalid expression

according to me here should be problem. its need to mention all field in group by

RDetails:

LOAD [IDETAILS KEY],

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

group by   [IDETAILS KEY] ;

neetha_p
Honored Contributor

Re: Aggregate SUM - invalid expression

Hi Chauhan,

Getting invalid expression

chauhans85
Esteemed Contributor

Re: Aggregate SUM -  invalid expression

RDetails:

LOAD [IDETAILS KEY],

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

group by   [IDETAILS KEY],[IndDet Count] ,[ACCURATE],[VALID],([COMPLETE];

neetha_p
Honored Contributor

Re: Aggregate SUM - invalid expression

still invalid expression

Community Browser