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

    Address1 and Address2 fields not populating correctly from calculated address field

    MonUserCJ _

      Hi,

       

         I trapped some address information that contains phone numbers (in order to account for the issue of addresses being longer when there is a suite number). Since the phone numbers contain "(", I used Lsplit to split out the phone numbers, creating a calculated field that just contains addresses.

       

         The issue is, when I use the address block feature to parse the address information, the Address1 and Address2 fields do not get correctly populated. Instead, all of this information gets put (along with the city name) in the city field. State and zip code do get parsed correctly, however.

       

         I think it may be because the Lsplit didn't carry over the linebreaks in the calculated field. Does anyone know what might be causing this issue, and how to fix it? I would appreciate any advice that anyone has.

       

         Thanks.

        • Address1 and Address2 fields not populating correctly from calculated address field
          Data Kruncher

          Hi CJ,

           

          The address block feature will need the line breaks in order function properly.

           

          Use Chr(13) to insert breaks at the appropriate locations in your calculated field and the address block handler should be able to do a much better job for you.

           

          Field1 + Chr(13) + Field2 + Chr(13) + Field3[/CODE]

           

          Kruncher

            • Address1 and Address2 fields not populating correctly from calculated address field
              MonUserCJ _

              Hello,

               

                 Thanks for the reply. It's good to know how to insert linebreaks into a calculated field. It turns out that the address block feature seems to work fine when I use it on the original field with both the addresses and phone numbers, so that's cool. One thing I was wondering about is if there is an address block field that catches the data that it wasn't able to parse into address information (in this case, the phone numbers). I can use LSplit to get the phone numbers, of course, but I'm curious.

               

                 Thanks.

                • Address1 and Address2 fields not populating correctly from calculated address field
                  Olly Bond

                  Hello CJ,

                   

                  There's an error code field, which might return what you need although I suspect that's just a flag in case Monarch can't parse anything from the block.

                   

                  You could fairly easily construct a "give-me-the-rest-of-the-address" field by making a calculated field with a few if(instr(textline( functions.

                   

                  I had a horrible job to do once for a client who wanted some sort of fuzzy matching between two sets of addresses. I'm sure there are tools out which can do that out of the box, but we had to build it in Monarch and it was very fiddly. Matches were scored on whether the first n characters of one line of Address A matched appeared in the corresponding line of Address B. Then the matches for all the lines were totalled for each address.

                   

                  If you want to see some of the formulae from these models I think that Steve might be able to dig them out - I'm afraid I don't have them anymore.

                   

                  Best wishes,

                   

                  Olly

                  • Address1 and Address2 fields not populating correctly from calculated address field
                    Grant Perkins

                    Hello,

                     

                    Thanks for the reply. It's good to know how to insert linebreaks into a calculated field. It turns out that the address block feature seems to work fine when I use it on the original field with both the addresses and phone numbers, so that's cool. One thing I was wondering about is if there is an address block field that catches the data that it wasn't able to parse into address information (in this case, the phone numbers). I can use LSplit to get the phone numbers, of course, but I'm curious.

                     

                    Thanks.[/quote]

                     

                    It is worth remembering that name and address databases are rarely more than 80 percent accurate and that formats for addresses can be extremely variable compared to any logical rule base that one might code. Sonme countries, the UK for example, are less structured than others.

                     

                    You will always have exceptions - hence the Monarch error Flag so that potentially erroneous records can be assessed as part of the QA process. If you know what all lthe likely you can use the error codes to identify those records for which alternative processing will be required and then perhaps use Monarch to deliver that alternative. Or suggest referral to a human.

                     

                    For anyone using V9 or later and using text blocks for anything, including addresses, where separation by original line or identifiably content might be useful in some cases the TEXTLINE() function is worth remembering.

                     

                     

                    HTH.

                     

                     

                    Grant