6 Replies Latest reply: Jul 17, 2013 9:14 AM by Bruce Tedder RSS

    Sum if LOAD

    Bruce Tedder

      Hi Guys

       

      The attached is an image of a table I loaded called QT.

       

      How coudl i get this:

       

      Type      | QT_NUM      | Value

      AV          254429          xyz

       

      where xyz is result of:

       

      (If CATEG = 0  then sum QT_CHARGE+QT_MARKUP+QT_ADJUST) + (if CATEG <>0 then sum QT_MARKUP+QT_ADJUST)

       

      Thanks

        • Re: Sum if LOAD
          Kaushik Solanki

          Hi,

           

               Try this.

           

               Load Type     ,

                        QT_NUM     ,

                        Sum(if(CATEG = 0,QT_CHARGE+QT_MARKUP+QT_ADJUST, QT_MARKUP+QT_ADJUST)) as Sum

               From xyz.

           

          Regards,

          Kaushik Solanki

            • Re: Sum if LOAD
              Bruce Tedder

              Hi Kaushik

               

              Sorry, but I get an invalid expression:

               

              Script:

              TEST:

              NoConcatenate

              LOAD

                  QT_NUMBER as JobNum,

                  Sum(if(Categ = 0,QT_CHARGE+QT_MARKUP+QT_ADJUST, QT_MARKUP+QT_ADJUST)) as Value

              Resident QT;

               

              exit script;

                • Re: Sum if LOAD
                  Kaushik Solanki

                  Ohh I m sorry i forgot to give group by .

                   

                  Try this.

                   

                       Load Type     ,

                                QT_NUM     ,

                                Sum(if(CATEG = 0,QT_CHARGE+QT_MARKUP+QT_ADJUST, QT_MARKUP+QT_ADJUST)) as Sum

                       From xyz Group by Type,QT_NUM    ;

                   

                  Regards,

                  Kaushik Solanki

                    • Re: Sum if LOAD
                      Bruce Tedder

                      Thanks the Group by Fixed it, BUT

                       

                      I would like only the QT_NUMBER as JobNum and Value.  How do you sum the nested If statements?

                       

                      TEST:

                      NoConcatenate

                      LOAD

                          QT_NUMBER as JobNum,

                          Sum(

                           If(Categ=0,Sum(QT_CHARGE+QT_MARKUP+QT_ADJUST),

                          If(Categ=1,Sum(QT_MARKUP+QT_ADJUST),

                          If(Categ=3, Sum(QT_MARKUP+QT_ADJUST))))

                           ) as Value

                          Resident QT

                      Group By QT_NUMBER,Categ;

                    • Re: Sum if LOAD
                      mayilvahanan ramasamy

                      Hi

                       

                      Try like this

                       

                      Try this.

                       

                           Load Type     ,

                                    QT_NUM     ,

                                    Sum(if(CATEG = 0,QT_CHARGE+QT_MARKUP+QT_ADJUST, QT_MARKUP+QT_ADJUST)) as Value

                           From xyz

                      Group by Type, QT_NUM;

                       

                       

                      or

                       

                      Try this.

                       

                           Load Type     ,

                                    QT_NUM     ,

                                    if(CATEG = 0,QT_CHARGE+QT_MARKUP+QT_ADJUST, QT_MARKUP+QT_ADJUST) as Value

                           From xyz;

                       

                      Then in expression =sum(Value)

                       

                      Hope it helps

                  • Re: Sum if LOAD
                    Mohit Sharma

                    try this

                    if(CATEG <> '0', QT_MARKUP+QT_ADJUST,QT_CHARGE+QT_MARKUP+QT_ADJUST)) as Sum