3 Replies Latest reply: May 15, 2014 10:01 AM by Grant Perkins RSS

    Address Block Help

    nelson _



      I'm trying to extract the Name, Address, City, State, Zip, and Country but for the following data it's not picking up anything after the address.  Most of my other client's are fine. Also, none of them are recognizing the Country.  It's taking the phone# instead for the Country.  Any advice?


      ABC Company, Inc.                              

      ABC Account Payable 2008 Lakeview Blvd,

      Richardson, TX, 75082, United States     

      Phone: (555) 555-2222                    

      Fax: (555) 555-2223

        • Address Block Help
          Data Kruncher

          Hi Nelson,


          I was able to get the address block to work properly by creating a footer template for the phone numbers, but that's probably not a real solution for your full report. So...


          Instead I extracted the whole thing as a single field, (End of Left Justification). Next, I created a calculated field to extract the company and address from that larger field, and gave it some formatting (new lines - CHR(13)) so that the address block will work properly with it.


          So the NameAddress field has this expression:



          You can always use functions to pull out the phone and fax numbers from the FullData field too.


          You could also adapt the NameAddress formula to handle address blocks longer than three lines by checking for the words Phone or Fax in the line before adding it to the derived address block.





          if(instr("Phone",textline(FullData,4))=0 .and. instr("Fax",textline(FullData,4))=0,textline(FullData,4),"")chr(13)/SIZE

          if(instr("Phone",textline(FullData,4))=0 .and. instr("Fax",textline(FullData,5))=0,textline(FullData,5),"")[/SIZE]



          Add more IFs as necessary, changing the line number as you go.


          Now you can use the Address Block feature on the NameAddress field.





          Edit: Of course it occurs to me one minute after posting that you could avoid the whole phone number trouble with LSplit on the "Phone" text...

            • Address Block Help
              nelson _

              I've narrowed things down to 3 lines that actually has useful information for my purposes.  I've set the address block to only work on line 3.  Using the following two examples my results would work perfect for the first example but yield nothing for the second since the third line is essentially blank.  How can I get it to "know" when an address line is missing and go straight to the City, State, zip, country?



              ABC Financial, Inc.                

              123 ABC Financial Ctr,             

              Minneapolis, MN, 55402, United States     



              XYZ Express                   

              New York, NY, United States        


                • Address Block Help
                  Grant Perkins



                  You probably cannot get the results you want from all records. In my experience most name and address databases are, at best, about 80% reasonably accurate and following the sort of rules that are required for automation. (Automation that is potentially easier with US formats than with UK formats I might add.)


                  The system works on a pattern recognition (in simple terms) and the key to the pattern is often some form of identifiable ZIP code. Absent that there is little to work with although there are some specialist programs that will attempt to match partial addresses to known records. The mapping and satnav systems use such things for example, but from a controlled point of input by the user.


                  Monarch allows you to report errors - or suspect interpretations. So at least you can get a reduced task list quite easily ready for manual assessment.


                  IF, and that is a big if, you can spot regular error types then you COULD write some extra rules and automate more of the process using calculated fields. Writing the formulas is not difficult. Working out the rules probably is.


                  You would need to work with the 2 or 3 line block and let Monarch ascertain which lines count for what. But without a zip code and known structure there is little to work with as your second example record shows.


                  If you have not already done so I would suggest reading the Help related to th Address Block Wizard and Using Address Blocks.  It is quite comprehensive.


                  There is also a special  address block TRAP based on ZIP codes but you should NOT be interested in that for your purposes.