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

# nest if statement help

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

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

Hi Olly

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

• ###### nest if statement help

It does work, at least on my PC.

There's a PRF online at: http://www.greenbar.info/examples/adonis.prf[/url]

HTH,

Olly

• ###### nest if statement help

thanks Olly you are a brother,

figured a way to do 2 queries in Access MDB to do a join that does the same thing i guess,

have a good day brother