5 Replies Latest reply: May 15, 2014 10:04 AM by Grant Perkins RSS

    If functions.. Please help..

    jkl123 _

      I'm trying to do multiple replace functions within one calculated field using an "If" function: If(VIA='*','NET',If(VIA='&','VOICE','OTHER')). Only the first "If" function is working. It doesn't seem to recognize the last two. Any suggestions? Thanks.

        • If functions.. Please help..
          Olly Bond

          Hello JKL,

           

          I think the '*' might be throwing Monarch a little, as * is a wildcard matching any string.

           

          Try replacing it with:

           

          IF(VIA=chr(42);TrueValue;(IF(VIA="SOMETHING ELSE";NextValue;...))

           

          Best wishes,

           

          Olly

            • If functions.. Please help..
              Olly Bond

              Hello JKL,

               

              Sorry, posted too soon.

               

              Apart from that suggestion, for larger collections of nested IFs it's much easier to create a calculated field of type "lookup". This way you can read in all the input values in your report and assign output values to them, as well as specifying rules when no value is found etc.

               

              It can save you from a lot of painful debugging...

               

              Best wishes,

               

              Olly

                • If functions.. Please help..
                  jkl123 _

                  "" is part of the report. However "" mean something. If a "*" is showing then I want it to say "NET" and if a "&" is showing I want it to say "VOICE". It's only looking at the first if function. Thanks

                    • If functions.. Please help..
                      Olly Bond

                      Dear JKL,

                       

                      If there are only two choices, then it's easy:

                       

                      IF(VIA="&";"VOICE";"NET")

                       

                      As & doesn't have any wildcard meaning, there's no need to use the chr(42) substitution to handle the *.

                       

                      And with only two choices, there's no need for a nested IF or for an internal lookup.

                       

                      HTH,

                       

                      Olly

                        • If functions.. Please help..
                          Grant Perkins

                          You could try a slightly different approach to see if it gives an alternative result, but I would also seek to put your "*" as the last selection

                           

                          If(VIA="&","VOICE","OTHER")+

                          If(VIA="*","NET","OTHER")

                           

                          It may still be advisable to make both the & and the * selections by ASCii character number. At last this way you can easily check both selection options independently.

                           

                          HTH.

                           

                           

                          Grant