6 Replies Latest reply: May 15, 2014 10:14 AM by djbeam _ RSS

    Calculated lookup field with incorrect result

    djbeam _

      I have a calculated field with several strings that it must lookup and search for and then return a value.

       

      I have also indicated that when it does not find a hit, that it should return "Other"

       

      I have also indicated that if the input file is NULL that it returns a "Other2".

       

      I have many records that the returned values appears to be nothing ... (ie nothing displays) .. ..

       

      What should I investigate as this result is not correct?

       

      Don

        • Calculated lookup field with incorrect result
          Grant Perkins

          Hi Don,

           

          As a starting point.

           

          Have you ascertained that the calculated field format (width, data width etc,) will assuredly work with all aspects of the calculation and that the display saize will not mask anything? Simplistic I know but something simple to check and easy to overlook.

           

          I'm always wary about relatively complex formulas. Faced with this problem I would create separate simple formulas for each of the selection criteria to ensure they always works (with the incoming data) as expected, especially with very large amounts of data.

           

          I would then build up to the more complex formula by combining 2 parts of it and checking that that works effectively, then add a third part and so on until it either all works or it fails.

           

          Once each of the individual calculations is know to work I would create the multiple selection formula by adding a reference to the calculated field that works rather than the formula within the field. Add one field at a time as suggested above. Once happy you could leave things like that if you are not at the ,i,its of the number of fields allowed - it would make for easier future maintenance for example. Just hide the intermediate fields. However if you wanted to redice to the single required field then cut and paste the separate formulas and replace the field names in the aggregated formula with the formaula that the work field contains and make sure everyting is OK before eliminating the work field.

           

          I would probably add each new working formula section to the final comobined fieldby cut and paste

           

          (If the activity relates to filtering I would most likely use the individual filters in a compound filter.)

           

          If you have already been through this process than is sounds like there may be some internal glitch somewhere'

           

          If data volumes are large you may need to investigate whether technical limits are in play.

           

          Alternatively we may need to check some aspects of software update status at the Windows level and so on. It's difficult to know with these things. This thread my become part of an iterative process.

           

          But let's start with the basics - unless you are sure you already have those covered.

           

           

          Grant

            • Calculated lookup field with incorrect result
              djbeam _

              there was a reply to this post .. does anyone else see the reply

              • Calculated lookup field with incorrect result
                djbeam _

                Okay .. I have done more testing ...

                 

                I have copied the xmod file so that I can reference it later. (old.xmod)

                 

                The lookup table had 73 entries in it.

                 

                I have removed all filters .. no difference

                 

                I then started at the bottom of the table and removed approx 15 at a time to see if there was any change ... there was not

                 

                When I had no entries in the lookup ... there was still nothing showing properly in the resultant field

                 

                I then added one new element to the lookup table ... and voila .. it worked.

                 

                I then saved the xmod file as new.xmod.

                 

                I have a tool that compares text files, so I  then compared the old.xmod to new.xmod and the only differences were the elements that were no longer part of the table.

                 

                This looks like a bug to me ..

                  • Calculated lookup field with incorrect result
                    djbeam _

                    Okay .. found the problem ....

                     

                    In the xml file of the model (ie the .xmod file) there was an invalid entry at the very end of the lookup table ...  that I am amazed that Monarch does not edit it out.

                     

                               

                     

                    which told Monarch that if no previous hit and as this indicates to check for nothing (which means everything else) then put "" (or nothing) in the resultant field ... which it did ....

                     

                    Manually removed that line .. and all is well again ...

                      • Calculated lookup field with incorrect result
                        Grant Perkins

                        Hi Don,

                         

                        Just to confirm what you found ...

                         

                        You had an internal lookup table that was encoding 73 possible values to a smaller set of (grouped) values which the model then used for a specific purpose.

                         

                        You have discovered that there was a non-obvious (difficult to spot) 74th value line at the end of the real values that would have looked like blanks on screen - or just the next Look up table line to be filled should that be necessary. However it was being treated as a genuine value which it turned out completely overwrote (in effect) all the previous values that would have been correctly interpreted.

                         

                        Removing the incorrect 74th entry has resolved the problem.

                         

                        Is that a satisfactory layman's summary?

                         

                        Or was this an external lookup file? (The way you have written the description it seems to be internal.)

                         

                        Have you any idea how the lookup was generated? Manual entries? Cut and Paste from somewhere else? Automatic value discovery from a sample report?

                         

                        I can see why there would be a problem and knowing the source might help us to easily help others who are stumbling into the same problem. This would be especially the case if generating the lookup table a certain way is a major contributory factor to the problem appearing.

                         

                        Have to say I can't recall this one coming up before but maybe it has and I have forgotten.  Or it has but the collective knowledge sources here have never worked out what was going on at the time. It would be easy to miss especially when not actually working with the model and data involved.

                         

                        Anyway, excellent analysis Don and I'm glad you got resolution  fairly quickly.

                         

                        BTW, your post #3 above - was that related to your first post (#1) or mine (#2)?

                         

                         

                        Grant

                          • Calculated lookup field with incorrect result
                            djbeam _

                            Grant, thanks for the comments ..

                             

                            Re table ... it is an internal table one ... not an external file ...

                             

                            My IT background has taught me over the years to deal with the source data to find problems .. not the program ... as the program only interprets the data .. thus the reason for the xlm search ...

                             

                            I believe the actions that caused the problem was the addition and deletion of elements in the lookup table  .. to a point were one with nothing in the lookup field or nothing in the resultant field was taken as the last element is the table that is used to enter new info ... As I indicated .. this is a situation where the software should audit and warn against it as the options to the right of the table take "blank"/"null" into consideration ... but not the table entries...

                             

                            re my #3 it was related to #2 .. I had a detailed reply .. but I do not see it .... must be in the bit bucket ....

                             

                            Again .. thanks for your help .. Don