2 Replies Latest reply: May 15, 2014 9:53 AM by Deborah99C _ RSS

    Null value in external lookup column

    Deborah99C _

      Please help!

      I have a null value in a single cell of an external lookup column imported to model#2.  All other cells of the column have valid data.  The external lookup data is coming from model#1, exported as an Excel file. I am doing a comparison from prior to current quarter for each item.  Example format for model#1 table view:


      ST   LOCALITY     EMP TX

      PA   Lancaster    522.91


      Example format for model#2 table view:


      ST   LOCALITY    EMP TX   EMP TX (ext lkup)

      PA   Lancaster   522.91   (Null)


      I have already checked my report view, and the templates are capturing the necessary data.  I have checked to see that the formatting is the same for each column.  There is a third column that calculates the difference between the two in model#2, but of course it also has a null value in this same cell. I checked Monarch Help but couldn't find anything pertaining to my situation. 

      Any help would be appreciated!





        • Null value in external lookup column
          Grant Perkins



          A null value in a field is most often the result of an attempt to write a non-numeric value into a numeric field.


          In your case you can check the look up table created using model #1 and see, based on your example, a valid numeric value.


          If model #2 is simply looking up the result of model #1's output (and that is know to be OK) then assuming the field types match and so on (is model #1 output to a text file or Excel or something else?) there woould seem to be little reason for the lookup data to result in a NULL field  UNLESS for some reason that particular line has no match on the key(s) (And the "EMP TX ext lkup" is a numeric field).


          Sometimes the keys may look the same but for some reason are different - a leading space for example or something like that.


          Have you checked through step by step? Try something like adding the key fields to the lookup as well - if they come through blank you have a mismatch.






          • Null value in external lookup column
            Deborah99C _



            I went over both models and verified the formatting was fine.  Then I checked the keys - and there was a mismatch.  Once I fixed that, the null values were gone.


            Thanks so much for your help!