4 Replies Latest reply: Jul 15, 2015 9:31 AM by Sunny Talwar RSS

    If in Script

    Gareth Cox-Thorpe

      This is my script,

       

      load%_BNO,

       

      if(UNIT.GRADEFIX = '' , '', num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) +  Avg(UNIT.CAPCLEAN_ORIG /1000))) as Buyer.GCT_Score

        

      FROM

      [$(QVD_Path)\BUYER_unit.qvd]

      (qvd)

       

      group by %_BNO;

       

      It's again saying invalid expression?

       

      Thanks

       

      Gareth

        • Re: If in Script
          Alessandro Saccone

          You need an aggregation function for

           

          if(UNIT.GRADEFIX = '' , '', num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) +  Avg(UNIT.CAPCLEAN_ORIG /1000))) as Buyer.GCT_Score

           

          something like

           

          Avg(

          if(UNIT.GRADEFIX = '' , '', num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) +  Avg(UNIT.CAPCLEAN_ORIG /1000))) as Buyer.GCT_Score

          )

           

          ... even if this expression (the one I've written) doesn't work

          • Re: If in Script
            Sunny Talwar

            Try this:

             

            Load%_BNO,

            if(UNIT.GRADEFIX = '' , '', num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) +  Avg(UNIT.CAPCLEAN_ORIG /1000))) as Buyer.GCT_Score

            FROM

            [$(QVD_Path)\BUYER_unit.qvd]

            (qvd)

            Group By %_BNO, UNIT.GRADEFIX;

              • Re: If in Script
                Gareth Cox-Thorpe

                Thanks sunindia,

                 

                What I'm trying to do is if the avg(Unit.Gradefix = '' then make it a 2 otherwise use avg(unit.gradefix)

                 

                My expression is below however I don't think it's correct.

                 

                Load%_BNO,

                if(Avg(UNIT.GRADEFIX = '' , 2 + num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) +  Avg(UNIT.CAPCLEAN_ORIG /1000))

                ,

                num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) +  Avg(UNIT.CAPCLEAN_ORIG /1000))) as Buyer.GCT_Score

                FROM

                [$(QVD_Path)\BUYER_unit.qvd]

                (qvd)

                Group By %_BNO, UNIT.GRADEFIX;