9 Replies Latest reply: Jul 22, 2015 8:11 AM by Frank Pappalardo RSS

    If, Then, Null?

    rwells7947 _

      Hello All,

       

      I've been revitalizing the use of Monarch in the departments I work with and have recently run into an interesting problem.

       

      We have several mainframe reports that have variable multi-line values; we've conquered these and use the textline function to break out the values into proper fields.

      Lately we've taken the idea a step further by using calculated fields to qualify what type of action occured on the account to create an actual field for a specific event (example below).

       

      Original multi-line Date Field ---> Textline parse of original field -


      > if statement using textline parse field to create multiple fields

       

      The problem i'm having is that I need my If statement to return a null if it finds nothing in the desginated fields but so far the only statement I can get to "take" is an else "" and I really need this to be a short date field...

       

      Do I have any hope aside from creating a calculated field to convert this character field to a date field?

       

      Any help or insight would be greatly appreciated.

       

      Regards,

       

      Robert

       

       

      Example of If statement:

      If(="AX",[AC Date 1],If(="AX",[AC Date 2],If(="AX",[AC Date 3],If(="AX",[AC Date 4],If(="AX",[AC Date 5],If(="AX",[AC Date 6],If(="AX",[AC Date 7],If(="AX",[AC Date 8],If(="AX",[AC Date 9],If(="AX",[AC Date 10],If(="AX",[AC Date 11],If(="AX",[AC Date 12],If(="AX",[AC Date 13],If(="AX",[AC Date 14],If(="AX",[AC Date 15],"")))))))))))))))

        • If, Then, Null?
          Olly Bond

          Hello Robert,

           

          If (condition ; result ; 1/0) should give you a null field. Alternatively using ctod("") might give you a null date also, although I've not tried that route...

           

          Best wishes,

           

          Olly

            • If, Then, Null?
              joey

              You might also consider using an internal lookup table isntead of the nasty nested if statement you have in your example.

                • If, Then, Null?
                  rwells7947 _

                  Hi Olly and Joey, thank you both for your suggestions.

                   

                  The CTOD("") works with the if statement although it produces an error when you attempt to test (which doesn't bother me).

                   

                  I'd love to do away with as many nested if statements as possible so I am intrigued by the idea of a lookup table...can you either of you gentlemen tell me more about how lookup tables work and are setup in Monarch (I'm using 10.0 pro). If necessary I'll hit the help file, I'm just asking in case there's a better resource.

                   

                  I love this product and the members of this forum are awesome!

                   

                  Thanks again,

                   

                  Robert :cool:

                  • If, Then, Null?
                    Data Kruncher

                    Replacing nested If with lookup tables is always a good idea, but because Robert is using multiple fields for the calculation, I'm not certain that a lookup table is a feasible option in this case.

                     

                    If only one of your 15 conditions can the AX value at the same time, then restructuring your expression might make it more readable and equally functional (while decidedly lengthy):

                     

                    If(

                    If(="AX",[AC Date 1],"") +

                    If(="AX",[AC Date 2],"") +

                    If(="AX",[AC Date 3],"") +

                    If(="AX",[AC Date 4],"") +

                    If(="AX",[AC Date 5],"") +

                    If(="AX",[AC Date 6],"") +

                    If(="AX",[AC Date 7],"") +

                    If(="AX",[AC Date 8],"") +

                    If(="AX",[AC Date 9],"") +

                    If(="AX",[AC Date 10],"") +

                    If(="AX",[AC Date 11],"") +

                    If(="AX",[AC Date 12],"") +

                    If(="AX",[AC Date 13],"") +

                    If(="AX",[AC Date 14],"") +

                    If(="AX",[AC Date 15],"")="",1/0,

                    If(="AX",[AC Date 1],"") +

                    If(="AX",[AC Date 2],"") +

                    If(="AX",[AC Date 3],"") +

                    If(="AX",[AC Date 4],"") +

                    If(="AX",[AC Date 5],"") +

                    If(="AX",[AC Date 6],"") +

                    If(="AX",[AC Date 7],"") +

                    If(="AX",[AC Date 8],"") +

                    If(="AX",[AC Date 9],"") +

                    If(="AX",[AC Date 10],"") +

                    If(="AX",[AC Date 11],"") +

                    If(="AX",[AC Date 12],"") +

                    If(="AX",[AC Date 13],"") +

                    If(="AX",[AC Date 14],"") +

                    If(="AX",[AC Date 15],""))

                    /codeAlternatively, I'd split this into two fields; one a character field to return either "" or the required date (as a string using DtoC), and the other to finally convert "" to a null, or the string date to a short Date using CtoD.

                      • If, Then, Null?
                        Data Kruncher

                        in case there's a better resource[/QUOTE]

                         

                        Well, if you're being so kind as to open the door, I may as well walk through!

                         

                        [URL="http://********************/calcfield/comparing-monarchs-internal-and-external-lookups"]On comparing internal and external lookups[/URL], which should provide the insight that you need.

                         

                        Questions?

                          • If, Then, Null?
                            rwells7947 _

                            Good Afternoon Gentlemen, it's always good to hear from you two!

                             

                            Grant, you are correct, I'm breaking a potential 15 value multiline into pieces then scanning it for the codes and dates I need.

                            Unfortunately, the end users want the 15 lines stored individually but I like the look of your code suggestion and will look to see if I can incorporate it.

                             

                            Data Kruncher, I have your website as a permanent favorite and intend to include it as a reference on internal training materials (with proper citation of course)!  lol The help file, this forum, and your site are the means I used to learn Monarch.

                             

                            Thanks to the help you both (and Olly) provided previously, a single model replaced 2 access databases and several hours of work...We can now process a report with 2 million records in about 15 minutes (including 70 calculated fields).

                             

                            I apparently have much more to learn.

                             

                            Thank you all for your help!

                             

                            Robert

                              • If, Then, Null?
                                Data Kruncher

                                That's music to my ears, Robert! I'm thrilled to hear that you find the site to be an invaluable part of your experience with Monarch. :cool:

                                 

                                The more I learn, the more I realize that there's much more to learn. It's all good fun.

                                 

                                You made an important point: it's not just what you can do, but also how quickly the work can be done. For me, the first big personal win was in replacing literally hours of detailed and stressful work with a few minutes of Monarch work together with Excel to produce exactly the same end result.

                                 

                                IMO, the opportunity is in taking the time that you get back and doing even more with it.

                                 

                                Enjoy,

                                Kruncher

                        • If, Then, Null?
                          Grant Perkins

                          Robert,

                           

                          I think Olly gives the answer to your main question here but reading the description of what you are doing I was just wonder which way you are using TextLine.

                           

                          Am I right to assume that you have set up some fields for each of the CODE values - so say 15 fields - and you then wish to populate the field for "AX" with its corresponding date IF one of the lines contains the code AX at a certain position in the line? I assume implicitly that only one line will have the AX code in any individual record.

                           

                          If my guess is wrong you can stop reading here!

                           

                          If not you could consider the 'Search' mode of TextLine which should eliminate the need to use such a long nested IF statement.

                           

                          So late's say you have a line something like

                           

                          AX 01/01/2010

                           

                          that might appear in any of the 15 or so line that may exist for the record.

                           

                          TEXTLINE(THE_FIELD),"AX")

                           

                          would return

                           

                          AX 01/01/2010

                           

                          If you just wanted the date ....

                           

                          LSPLIT((TEXTLINE(THE_FIELD),"AX"),2,"",2)

                           

                          would give you just the date (... as text. You may need to manipulate it with additional findtions to give a different format if that is what you want.)

                           

                          If the uniqueness of "AX" relates to its position on a line - which I might well do in some situations - then you can apply other functions to create an Expression that specifies the positional uniqueness for the search as a substitute for the simple "AX" input.

                           

                          lsplit(textline(The_Field,substr(The_Field,3,2)="AX"),2," ",2)[/SIZE]

                           

                           

                          gives just the date from the example line

                           

                          ZZAX 01/01/2010

                           

                          .

                           

                          I don't know that this will be of any use to you but though I would offer it as a suggestion just in case it is.

                           

                           

                          Grant

                          • Re: If, Then, Null?
                            Frank Pappalardo

                            "1/0" - So simple, thank you! I had stumbled onto something with the FLOOR function a while ago but couldn't rememeber how I did it. This is so much better!

                             

                            PS It still would be a good idea to have a "Null" keyword