topic Aggregate SUM - invalid expression in QlikView

Aggregate SUM - invalid expression

Anonymous — Sat, 26 Jul 2014 20:01:09 GMT

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;

Re: Aggregate SUM - invalid expression

qlikoqlik — Sun, 27 Jul 2014 06:06:57 GMT

Hi Nitha

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

Thanks and regards

Padma

Re: Aggregate SUM - invalid expression

Sun, 27 Jul 2014 15:28:46 GMT

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

Re: Aggregate SUM - invalid expression

Anonymous — Sun, 27 Jul 2014 17:50:05 GMT

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

Re: Aggregate SUM - invalid expression

Sun, 27 Jul 2014 20:44:41 GMT

Do you need this calculation in Script or UI ?

Re: Aggregate SUM - invalid expression

Anonymous — Mon, 28 Jul 2014 08:41:51 GMT

Hi dathu,

in script please.

Re: Aggregate SUM - invalid expression

SunilChauhan — Mon, 28 Jul 2014 08:50:27 GMT

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] ;

Re: Aggregate SUM - invalid expression

Anonymous — Mon, 28 Jul 2014 09:04:33 GMT

Hi Chauhan,

Getting invalid expression

Re: Aggregate SUM - invalid expression

SunilChauhan — Mon, 28 Jul 2014 09:06:53 GMT

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];

Re: Aggregate SUM - invalid expression

Anonymous — Mon, 28 Jul 2014 09:15:54 GMT

still invalid expression

Re: Aggregate SUM - invalid expression

SunilChauhan — Mon, 28 Jul 2014 09:28:25 GMT

could you please attched screen shot or sample file

Re: Aggregate SUM - invalid expression

Anonymous — Mon, 28 Jul 2014 09:47:57 GMT

Sorry,Its not possible

Re: Aggregate SUM - invalid expression

Mon, 28 Jul 2014 14:32:14 GMT

Please provide your Script and what exactly Error Message ?

Re: Aggregate SUM - invalid expression

Anonymous — Mon, 28 Jul 2014 15:12:19 GMT

Hi Dathu,

Below is script :

RDetails:

LOAD *,
sum(if([INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) as INDVCOUNT,
sum(if([VALIDITY] = '100' AND [INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) as ValidCount,
sum(if([COMPLETENESS] = '100' AND [INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) as CompleteCount,
sum(if([ACCURACY] = '100' AND [INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) as AccurateCount,
sum(if([VALIDITY] = '0' AND [INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) as InvalidCount,
sum(if([COMPLETENESS] = '0' AND [INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) as IncompleteCount,
sum(if([ACCURACY] = '0' AND [INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) as InaccurateCount

Resident RIndDetails
Group By [DETAILS KEY],[INDVIVIDUAL COUNT],[VALIDITY],[COMPLETENESS],[VALIDITY];

error message : Invalid expression

Re: Aggregate SUM - invalid expression

Anonymous — Mon, 28 Jul 2014 15:14:12 GMT

if i give comma in place of AND operator,then also its giving in same error.

i even tried instead of using wildcard ,have listed all fields with no success.

Re: Aggregate SUM - invalid expression

Mon, 28 Jul 2014 15:18:43 GMT

If the RIndDetails more fields that your Group List, it shown InValid Error Message"

Try like below:

LOAD

[DETAILS KEY],

[INDVIVIDUAL COUNT],

[VALIDITY],

[COMPLETENESS],

     sum(if([INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) as INDVCOUNT,
     sum(if([VALIDITY] = '100' AND [INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) as ValidCount,
     sum(if([COMPLETENESS] = '100' AND [INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) asCompleteCount,
     sum(if([ACCURACY] = '100' AND [INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) asAccurateCount,
     sum(if([VALIDITY] = '0' AND [INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) as InvalidCount,
     sum(if([COMPLETENESS] = '0' AND [INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) asIncompleteCount,
     sum(if([ACCURACY] = '0' AND [INDVIVIDUAL COUNT] = '1',[DETAILS KEY])) as InaccurateCount
Resident RIndDetails
Group By

[DETAILS KEY],

[INDVIVIDUAL COUNT],

[VALIDITY],

[COMPLETENESS]

;

Re: Aggregate SUM - invalid expression

Anonymous — Mon, 28 Jul 2014 15:37:04 GMT

still no success,even when listed all fields in RIndDetails.

Re: Aggregate SUM - invalid expression

Mon, 28 Jul 2014 15:57:50 GMT

Why you are using String comparison in conditions? all these fields are converted into text ?

Try Below :

LOAD

[DETAILS KEY],

[INDVIVIDUAL COUNT],

[VALIDITY],

[COMPLETENESS],

sum(if([INDVIVIDUAL COUNT] = 1,[DETAILS KEY]),0) as INDVCOUNT,

sum(if([VALIDITY] = 100 AND [INDVIVIDUAL COUNT] = 1,[DETAILS KEY],0)) as ValidCount,

sum(if([COMPLETENESS] = 100 AND [INDVIVIDUAL COUNT] = 1,[DETAILS KEY],0)) as CompleteCount,

sum(if([ACCURACY] = 100 AND [INDVIVIDUAL COUNT] = 1,[DETAILS KEY])) as AccurateCount,

sum(if([VALIDITY] = 0 AND [INDVIVIDUAL COUNT] = 1,[DETAILS KEY],0)) as InvalidCount,

sum(if([COMPLETENESS] = 0 AND [INDVIVIDUAL COUNT] = 1,[DETAILS KEY],0)) as IncompleteCount,

sum(if([ACCURACY] = 0 AND [INDVIVIDUAL COUNT] = 1,[DETAILS KEY],0)) as InaccurateCount

Resident RIndDetails

Group By

[DETAILS KEY],

[INDVIVIDUAL COUNT],

[VALIDITY],

[COMPLETENESS]

;

Re: Aggregate SUM - invalid expression

Mon, 28 Jul 2014 15:58:19 GMT

If still not working, please post whole script not only this Part.

Re: Aggregate SUM - invalid expression

Anonymous — Mon, 28 Jul 2014 16:32:46 GMT

Hi Dathu,

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

[IDETAILS KEY],

RRCN_KEY ,

RR_KEY

RR_KEY,

PCDR_KEY

[PCDR KEY],

LCDR_KEY

[LCDR KEY],

RPCDR_KEY

[RPCDR KEY] ,

SCDR_KEY

[SCDR_KEY] ,

RCOMPLETE

[COMPLETE] ,

RVALID

[VALID] ,

RACCURACY

[ACCURATE] ,

RMESSAGE

[IMESSAGE],

'2'

[R DETAILS IDENTIFIER],

if (

RPIN_KEY = '-1',0,1)

[IndDet Count]

From

$(vQVDDataPath)RPIN.qvd(

qvd);

Concatenate

LOAD

RPIT_KEY

[IDETAILS KEY],

RRCN_KEY ,

RR_KEY

RR_KEY,

PCDR_KEY

[PCDR KEY],

LCDR_KEY

[LCDR KEY],

RPCDR_KEY

[RPCDR KEY] ,

SCDR_KEY

[SCDR_KEY] ,

RCOMPLETE

[COMPLETE] ,

RVALID

[VALID] ,

RACCURACY

[ACCURATE] ,

RMESSAGE

[IMESSAGE],

'3'

[R DETAILS IDENTIFIER],

if (

RPIT_KEY = '-1',0,1)

[IndDet Count]

From

$(vQVDDataPath)RPIT.qvd(

qvd);

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]))

IC,

sum(

if(

[VALID] = '100'

AND

[INDVIVIDUAL COUNT] = '1',

[IDETAILS KEY],0))

ValidCount,

sum(

if(

[COMPLETE]= '100'

AND

[INDVIVIDUAL COUNT] = '1',

[IDETAILS KEY],0))

CompleteCount,

sum(

if(

[ACCURATE] = '100'

AND

[INDVIVIDUAL COUNT] = '1',

[IDETAILS KEY],0))

AccurateCount,

sum(

if(

[VALID]= '0'

AND

[INDVIVIDUAL COUNT] = '1',

[IDETAILS KEY],0))

InValidCount,

sum(

if(

[COMPLETE]= '0'

AND

[INDVIVIDUAL COUNT] = '1',

[IDETAILS KEY],0))

InCompleteCount,

sum(

if(

[ACCURATE]= '0'

AND

[INDVIVIDUAL COUNT] = '1',

[IDETAILS KEY],0))

InAccurateCount

Resident RIndDetails

group

RRCN_KEY ,

RR_KEY,

[PCDR KEY],

[LCDR KEY],

[RPCDR KEY] ,

[SCDR_KEY] ,

[COMPLETE] ,

[VALID] ,

[ACCURATE] ,

[IMESSAGE],

[R DETAILS IDENTIFIER] ,

[INDVIVIDUAL COUNT],

[IDETAILS KEY];