RSA Archer - Multiple IF statements

Question

I'm hoping someone can help me

I have two fields with two different value lists; by choosing a value in Basel Business line 2 a value automatically populates Basel Business line 1.

I have created several IF statements, which validates correctly. However when testing this functionality, the First two IF statement work, so when I select "Advisory sales", Basel Business Line 1 automatically populates with "Corporate Finance" ie. However when I select "Card services" or any other values from If statement 3 or 4, no values is populated in Basel Business line 2.

IF((( OR( [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Corporate Finance"), [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Merchant Banking"), [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Advisory Services"), [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Municipal / Government Finance")), VALUEOF([Basel Business Line Level 1],"Corporate Finance")),

IF( OR( [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Sales"), [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Market Making"), [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Proprietary Positions"), [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Treasury")), VALUEOF([Basel Business Line Level 1],"Trading and Sales"))),

IF( OR( [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Retail Banking"), [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Private Banking"), [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Card Services")), VALUEOF([Basel Business Line Level 1],"Retail Banking")),

IF( [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Commercial Banking"), VALUEOF([Basel Business Line Level 1],"Commercial Banking")))

I can't figure out why the last two If statement aren't working.. Help please?

score 2 · Answer 1 · answered May 28 '16 at 17:16

The formula you have shared is not correct from logical stand point. I'm surprised that it passed validation. You may consider reporting this as Archer bug to RSA.

About the issue you experienced. See the image below. You enclosed 2nd IF into the parenthesis where condition for the first IF should be located. This broke the logic.
Every time you have troubles with calculation I suggest you format it in a similar way for easy troubleshooting.

I propose the following solution:

IF(
   OR(
      [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Corporate Finance"),
      [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Merchant Banking"),
      [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Advisory Services"),
      [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Municipal / Government Finance")
   ),
   VALUEOF([Basel Business Line Level 1],"Corporate Finance"),
   IF(
      OR(
         [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Sales"),
         [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Market Making"),
         [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Proprietary Positions"),
         [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Treasury")
      ),
      VALUEOF([Basel Business Line Level 1],"Trading and Sales"),
      IF(
         OR(
            [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Retail Banking"),
            [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Private Banking"),
            [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Card Services")
         ),
         VALUEOF([Basel Business Line Level 1],"Retail Banking"),
         IF(
            [Basel Business Line Level 2]=VALUEOF([Basel Business Line Level 2],"Commercial Banking"),
            VALUEOF([Basel Business Line Level 1],"Commercial Banking")
         )
      )
   )
)

*** Note, that if value of [Basel Business Line Level 2] field will not be matched by any OR, then this calculation will not return any value and field [Basel Business Line Level 1] will be set to "No Selection".
You may want to add one more line to the last IF to populate field [Basel Business Line Level 1] with value if all OR's failed to match [Basel Business Line Level 2] field.

I hope this helps, good luck!

RSA Archer - Multiple IF statements

1 Answers1