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;

[SunilChauhan]

could you please attched screen shot or sample file

[Anonymous]

Sorry,Its not possible

[#4]

Please provide your Script and what exactly Error Message ?

[Anonymous]

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

[Anonymous]

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.

[#7]

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]

;

[Anonymous]

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

[#9]

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]

;

[#10]

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

[Anonymous]

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

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

[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] ,

[INDVIVIDUAL COUNT],

[IDETAILS KEY];