23 Replies Latest reply: Jul 29, 2014 11:08 AM by neetha P RSS

    Aggregate SUM -  invalid expression

    neetha P

      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

          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
            Srikanth P

            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
              Sunil Chauhan

              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
                  neetha P

                  Hi Chauhan,

                   

                  Getting invalid expression

                    • Re: Aggregate SUM -  invalid expression
                      Sunil Chauhan

                      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
                          neetha P

                          still invalid expression

                            • Re: Aggregate SUM -  invalid expression
                              Sunil Chauhan

                              could you please attched screen shot or sample file

                                • Re: Aggregate SUM -  invalid expression
                                  neetha P

                                  Sorry,Its not possible

                                    • Re: Aggregate SUM -  invalid expression
                                      Srikanth P

                                      Please provide your Script and what exactly Error Message ?

                                        • Re: Aggregate SUM -  invalid expression
                                          neetha P

                                          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
                                              neetha P

                                              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
                                                  Srikanth P

                                                  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
                                                      neetha P


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

                                                        • Re: Aggregate SUM -  invalid expression
                                                          Srikanth P

                                                          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
                                                              Srikanth P

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

                                                                • Re: Aggregate SUM -  invalid expression
                                                                  neetha P

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

                                                                   

                                                                    • Re: Aggregate SUM -  invalid expression
                                                                      Srikanth P

                                                                      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]

                                                                      ;

                                                                    • Re: Aggregate SUM -  invalid expression
                                                                      neetha P

                                                                      Still issue persists