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;
could you please attched screen shot or sample file
Sorry,Its not possible
Please provide your Script and what exactly Error Message ?
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
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.
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]
;
still no success,even when listed all fields in RIndDetails.
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]
;
If still not working, please post whole script not only this Part.
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];