5 Replies Latest reply: May 15, 2014 10:14 AM by Grant Perkins RSS

    Monarch 8.02 - Problem with External Lookup

    Alca _

      Hi There,

       

      Hoping somebody can help with this.

       

      I have created two external lookups to two comma delimited text files. The first lookup works fine, bringing in what it's supposed to.

       

      However, the second lookup isn't. Having tracked the problem down to the fact that in the lookup, the "first row contains character column names" keeps checking itself, even after I have gone in, unchecked it, and saved the model (and project for good measure)

       

      Has anybody come across this? I haven't! - If you have, how did you fix it?

       

      I have tried various things:

       

      Deleted the lookup and rebuilt it.

      Compared the lookup to others that work (no differences)

      Rebooted my machine (in an act of desperation!)

       

      Any ideas????

       

      Thanks,

       

      Alan

        • Monarch 8.02 - Problem with External Lookup
          Grant Perkins

          Hi Alan and welcome to the forum.

           

          This sounds odd. Are these fixed files that you are looking up from or are they created dynamically for each run? Are you sure that the data content is consistent enough to work (i.e. character, or numeric or a date or whatever for lookup, no leading spaces, correct justification if character and so on.)

           

          Is the model newly created or is it one that you have been using for a while? If the latter, has it always worked previously?

           

          I'm tempted to suggest a few basic tests  - you may already have done them - in an attempt to try to ensure we don't miss any basic issues.

           

          Fristly, have you tried creating a fixed width version of the file (rather than delimited) and then defining a lookup against that?

           

          Have you tried creating the lookup as an internal lookup? If the lookup data is 2 in columns you should be able to cut and paste it into an internal lookup and then see if that works.

           

          One extremely rare possibility is that the model file is somehow corrupt in a way that always messes with the section of the model in which the secodn lookup definition is held. You could try recreating the model afresh - or at least the lookup parts of is and whatever you need to get to that point - to see it that changes anything.

           

          Obviously some sort of process of elimination is what we are aiming for from here.

           

          HTH.

           

           

          Grant

            • Monarch 8.02 - Problem with External Lookup
              elginreigner _

              I think I saw this problem, you're talking a few years back. I believe the solution was to use a header row in the lookup file, making the check relevant.

                • Monarch 8.02 - Problem with External Lookup
                  Alca _

                  Grant, Elgin,

                   

                  Thanks for the replies - Even after rebuilding the model the same issue arose. I think as Elgin pointed out the addition of a header will perhaps help. Problem I have though is that the extract is taken from a source data repository and it arrives without a header. I have something to go on now though. I have been away from Monarch for a couple of years and im going through a refresher process. Thanks for the help guys!

                   

                  Alan

                    • Monarch 8.02 - Problem with External Lookup
                      elginreigner _

                      My process did not have a header row either, it was a SQL export. I created a template that contained nothing but the header and combined both files. These were CSV files, which work well with DOS commands. If you try this route, in the DOS COPY command, specify the template file first or the header will be added possibly to the end of the file.

                        • Monarch 8.02 - Problem with External Lookup
                          Grant Perkins

                          My process did not have a header row either, it was a SQL export. I created a template that contained nothing but the header and combined both files. These were CSV files, which work well with DOS commands. If you try this route, in the DOS COPY command, specify the template file first or the header will be added possibly to the end of the file.[/QUOTE]

                           

                          This.

                           

                          Although in theory I would expect the model to be able to deal with this if using a CSV format PROVIDED the lookup fields were of he correct size and type.

                           

                          Basically there are two approaches that seem logical in this situation. Concatenate the incoming file to a fixed format header (or generate the file in fixed format to maximum field sizes at source) using a 'dos' script or define all the relevant lookup fields to the specific type and size in the model.

                           

                          The third possibility is that there is some irregular data in play and it needs to be identified and overpowered to ensure it does what you need it to do!

                           

                          If you have limited control over the incoming data it may be worth seeking a way to deal with any conceivable incoming eventuality rather than hardcoding for what is there at the moment. Once done such a development is usually rapidly adaptable for other challenges if required. So long as, ultimately, the system is doing the work how you get there may not be too important.

                           

                          HTH.

                           

                           

                          Grant