6 Replies Latest reply: May 15, 2014 9:57 AM by Deborah99C _ RSS

    Preceding 0 in a numeric field

    ron cousino

      I have a report that includes an account number field that I extract using Monarch Pro v7.2 and specify as a numeric field.  However when I do an external lookup to the database I get a mismatch since the field is zero filled in the database.  The report does not display the leading zeroes.  Is there a way in Monarch to create a calculated field to add the leading zeroes.

        • Preceding 0 in a numeric field
          Nick Osdale-Popa

          You will need to make a calculated field display a character field.  You can refer to this [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000361#000005"]thread[/url] on how to pad a number with zeroes.

           

          [size="1"][ May 01, 2006, 05:33 PM: Message edited by: Todd Niemi ][/size]

          • Preceding 0 in a numeric field
            ron cousino

            It is a numeric within the database.  By changing the field to a character in the extracted data when I go to do an external lookup in the database I do not get a match.

            • Preceding 0 in a numeric field
              Grant Perkins

              Ron,

               

              As I recall there is not much you can do directly with a database that supports fields with leading zeros as numerics.

               

              2 ideas come to mind.

               

              Either preprocess the lookup table to a new table or file and include a calcualted field that allows you to make the match and then use the calculated field as the lookup key

               

              OR

               

              Invert the process to that your 'Master' file is the one you really want to lookup from and you 'Look up table' is the data you wanted to lookup for. Do you see what I mean?

               

              You could then filter out any records where the matches are blank and the re-arrange the fields to get the output format you need.

               

              It may sound messy and there might reasons of record volume levels that make it impractical but is does, theoretically, give you back the opportunity to control the field with leading zeros from the database and make a calculated field that is compatible with the field on your other file.

               

              I can expand on the idea but it may be useful to do so with a little more detail on your requirement.

               

              Hope this suggests something to you.

               

              Grant

               

               

              Originally posted by ron cousino:

              It is a numeric within the database.  By changing the field to a character in the extracted data when I go to do an external lookup in the database I do not get a match. /b[/quote]