Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Still issue persists
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]
;
Hi Dathu,
Thanks,Its working , no error