4 Replies Latest reply: May 15, 2014 10:07 AM by adonis _ RSS

    nest if statement help

    adonis _

      gents, hope all is well with you,

      I am not great with complex nested if statements,

      Any idea how to create an if statement that does two things.

       

      When cancel code is a numeric number I want to ensure the debit code = C is not changed to a negative number

       

      reason being when cancel code is an alpha I want to multiply the commission against 1 or -1 depending on code

      ie:

       

      if(debit code = D, *1, *-1)

      i also need to use maybe a nested so that when cancel code is Alpha then to only change those Credits, otherwise if it is a numeric, leave it alone as those are not cancels,

      let me know what you think. 

       

      sample data:

      Commission     debit_credit_cd     Cancel Code     currency_cd

      0     C     1     000

      0     C     2     000

      0     C     A     000

      0     C     B     000

      0     C     J     000

      0     D     1     000

      0     D     2     000

      0     D     A     000

      0     D     B     000

      0     D     J     000

      0.01     C     J     000

      0.01     D     1     000

      0.02     C     1     000

      0.05     C     1     000

      0.05     C     A     000

      0.05     D     1     000

      0.13     C     1     000

      0.18     C     1     000

      0.25     C     1     000

      0.3     C     1     000

      0.32     C     1     000

      0.34     C     1     000

        • nest if statement help
          Olly Bond

          Hello Adonis,

           

          In general, creating a calculated field of the type "lookup" is a handy way to avoid lots of nested if statements.

           

          But here, perhaps it would be easier to use:

           

          NewCommission=

           

          if(IsAlpha()=0 .and. ="C";Commission;

          if(IsAlpha()=1 .and. ="C";-1*Commission;

          if(IsAlpha()=0 .and. ="D";Commission;

          if(IsAlpha()=1 .and. ="D";Commission;

          1/0))))

           

          This would give you a null value for entries where the debit or credit code was neither C nor D.

           

          HTH,

           

          Olly

            • nest if statement help
              adonis _

              Hi Olly

              Thanks for the reply,

              somehow not sure what is going wrong,

               

              when i use the formula it doesn't show me the intended output.

               

              let me give you an example of sample final output,

              you can see the last column for simplicity is called commission final adjustment, and what it needs to do is show the conditions provided.

              let me know what you think.

              i gave you examples where there are some alpha and numerics.

              when you see cancel code alpha, you will see the credits * -1

              otherwise if the cancel code is numeric credits are * 1

               

              Commission      debit_credit_cd     Cancel Code     currency_cd      Commission Final Adjustment

              1,346.64      C     J     008      (1,346.64)

              2,040.90      C     J     008      (2,040.90)

              10,513.13      C     J     008      (10,513.13)

              204,084.75      C     J     008      (204,084.75)

              2,607.75      C     A     008      (2,607.75)

              5,838.25      C     A     008      (5,838.25)

              10,297.43      C     A     008      (10,297.43)

              40,365.00      C     A     008      (40,365.00)

              108,585.00      C     A     008      (108,585.00)

              169,448.52      C     A     008      (169,448.52)

              -        D     1     008      -  

              0.02      C     1     008      0.02

              0.02      D     1     008      0.02

              0.29      C     1     008      0.29

              0.29      D     1     008      0.29

              0.33      C     1     008      0.33

              0.33      D     1     008      0.33

              0.35      C     1     008      0.35

              0.35      D     1     008      0.35

              0.55      C     1     008      0.55

              0.55      D     1     008      0.55

              0.58      C     1     008      0.58

              0.58      D     1     008      0.58

              1.98      C     1     008      1.98

              1.98      D     1     008      1.98

              2.07      D     1     008      2.07

              2.08      C     1     008      2.08

              2.14      D     1     008      2.14