13 Replies Latest reply: May 15, 2014 10:10 AM by Kamawell2 _ RSS

    Problems with a Summary

    Kamawell2 _

      I've got an Excel sheet that I'm opening in Monarch, mainly in order to make use of the filter and summary functions.   When I come to define the summaries however I'm getting a dialogue box that just says "Internal Error." It doesn't seem to matter what I put in the summary, I get the message regardless.

       

      The Excel sheet is not very big but there is an external lookup that points to another, rather larger Excel sheet.

       

      I've spent two days trying to get the spreadsheet for the lookup table right and figured that once I was on to the Monarch bit I'd be home and dry!  I'm no expert when it comes to Monarch but I use it just about daily and haven't come across this before. I've tried the same thing on another PC that's got the same version of Monarch (Pro 10.5) and I get the same result.  Can anyone advise or suggest where I might be going wrong please?

       

      Thanks

       

      Andy

        • Problems with a Summary
          Olly Bond

          Hello Andy,

           

          Have you tried saving the Excel file as CSV, which will remove any links to external files and just give you the data, and then opening this CSV in Monarch?

           

          Best wishes,

           

          Olly

            • Problems with a Summary
              Kamawell2 _

              Hello Andy,

               

              Have you tried saving the Excel file as CSV, which will remove any links to external files and just give you the data, and then opening this CSV in Monarch?[/QUOTE]

               

              Hi Olly

               

              I've tried that now but I get the same result.  There aren't any links in the Excel sheet itself - the lookup is done within Monarch.

                • Problems with a Summary
                  Grant Perkins

                  Hi Andy,

                   

                  Just to double check - you have exported the Excel content to a csv fiel, read in the csv as a 'database' that has lookup links back to the Excel spreadsheet. Is that correct?

                   

                  Does the resulting table look ok and load without error - you mention the error in respect of the summary but not the table unless I missed it somewhere.

                   

                  If the table looks to be OK it seems dd that the summary goes wrong UNLESS something has changed between the time the summary was defined and saved in the model and now. For example if an incoming field has changed type or format or something but that would only seem likely if you are always allowing Monarch to assess the incoming file for, for example, sizes and types of fields rather than fixing them in the model. Even then I would have thought you would see errors reported on import to the table. The other possibility is along the lines that a field set up as a measure is now importing as character and so is not going to work as a measure but your 'whatever I put in the summary' observation suggest things are not that specific.

                   

                  Which version of Excel? (just for reference ....)

                    • Problems with a Summary
                      Kamawell2 _

                      Hi Andy,

                       

                      Just to double check - you have exported the Excel content to a csv fiel, read in the csv as a 'database' that has lookup links back to the Excel spreadsheet. Is that correct?

                       

                      Does the resulting table look ok and load without error - you mention the error in respect of the summary but not the table unless I missed it somewhere.

                       

                      If the table looks to be OK it seems dd that the summary goes wrong UNLESS something has changed between the time the summary was defined and saved in the model and now. For example if an incoming field has changed type or format or something but that would only seem likely if you are always allowing Monarch to assess the incoming file for, for example, sizes and types of fields rather than fixing them in the model. Even then I would have thought you would see errors reported on import to the table. The other possibility is along the lines that a field set up as a measure is now importing as character and so is not going to work as a measure but your 'whatever I put in the summary' observation suggest things are not that specific.

                       

                      Which version of Excel? (just for reference ....)[/QUOTE]

                       

                      Hi Grant

                       

                      Yes, that's about it but I'll lay it out in a bit more detail in case there's something vital I've failed to mention so far.

                       

                      I've got a report in Excel format that's missing one vital field.  I want to use Monarch to take selected parts of my report, add this missing vital field using another, separate Excel sheet as a lookup source and then use filters and summaries to split the report up into 4 or 5 smaller reports.  I use Monarch to do this on a large number of reports every month and it's a vital part of the way we work in this office now.

                       

                      The Excel sheet is produced by another bit of (Oracle-based) software.  It doesn't have any links to anything else but, as Olly suggested, I've now taken that and saved it as a csv file.  I open that as a database in Monarch and apply a model to it on that inital screen.  The model has no summaries at this point.  Everything opens without any warning or error.  If I then go to create even the simplest summary with no filter and seemingly any combination of fields I get a dialogue box with "Internal Error."  This happens with a quick summary too.

                       

                      The version of Excel I'm using is 2003.

                       

                      As usual, I feel I'm missing something obvious as I've been using very similar models and summaries for some time without any problems!

                        • Problems with a Summary
                          Grant Perkins

                          Certainly seems odd if all looks well in the table.

                           

                          Are you still using V9 Pro?

                           

                          The internal error message is often accompanied by a MS Jet error number. Now in this case it sounds like it's not a Jet issue and so there may be no error number reported. Is that the case?

                           

                          Is the model yo uapply pre-defined. If so, have you tried an import and created a new model then found the same results in the summary? Do you get the problem when no external link is defined?

                           

                          If the basic process works with other examples (different Excel sheets, csv files, etc.) yet fails here from both Excel and csv sources it suggest there is something about the data (or the Excel file that is also exported to the csv file) but the only thing I can think of is that there are too many fields defined (or something similar in relation to system limits) that only shows up after the table has been loaded but when you try to move to the next step. Of course I am speculating here not knowing the input file.

                           

                          If you import with selectivity for minimal number of fields (as a test) do you stll get the same problem all the time?

                           

                          If we temporarily assume that there might be a data problem (invisible) selecting to import fields in small numbers might help to identify whether that idea has any validity and, maybe, provide a pointer to which fields are problematic.

                           

                          Also, from the csv file, a visual check by opening the file in notepad or wordpad and looking for oddities might throw something up. Sometimes it does, sometime not.

                           

                          I'm expecting to be out for the rest of the day and all of tomorrow but will try to check in later to see if there is any news.

                           

                           

                          Grant

                            • Problems with a Summary
                              Kamawell2 _

                              Certainly seems odd if all looks well in the table.

                               

                              Are you still using V9 Pro?

                               

                              The internal error message is often accompanied by a MS Jet error number. Now in this case it sounds like it's not a Jet issue and so there may be no error number reported. Is that the case?

                               

                              Is the model yo uapply pre-defined. If so, have you tried an import and created a new model then found the same results in the summary? Do you get the problem when no external link is defined?

                               

                              If the basic process works with other examples (different Excel sheets, csv files, etc.) yet fails here from both Excel and csv sources it suggest there is something about the data (or the Excel file that is also exported to the csv file) but the only thing I can think of is that there are too many fields defined (or something similar in relation to system limits) that only shows up after the table has been loaded but when you try to move to the next step. Of course I am speculating here not knowing the input file.

                               

                              If you import with selectivity for minimal number of fields (as a test) do you stll get the same problem all the time?

                               

                              If we temporarily assume that there might be a data problem (invisible) selecting to import fields in small numbers might help to identify whether that idea has any validity and, maybe, provide a pointer to which fields are problematic.

                               

                              Also, from the csv file, a visual check by opening the file in notepad or wordpad and looking for oddities might throw something up. Sometimes it does, sometime not.

                               

                              I'm expecting to be out for the rest of the day and all of tomorrow but will try to check in later to see if there is any news.

                               

                               

                              Grant[/QUOTE]

                               

                              No, we're on 10.5 Pro now.  I think I've updated my profile now.

                               

                              The model was already defined so, as suggested, I've remade it and tested it at each step, making summaries along the way.  The problem seems to be with the filters.

                               

                              I had 5 value-based filters, looking at the field - "Site Name" - generated by the external lookup.  The csv file I'm working on doesn't contain all the possible values of Site Name so I made a model using another report that did, defined the filters I needed and imported them into my new model. Once these are imported I start getting the summary problems.  I've deleted the imported versions and made a new filter based on the same field and get the same problem.  If I make a similar filter based on another (non-lookup) field, everything is OK. 

                               

                              An obvious work-around is to export the non-filtered table view that includes the lookup field, reopen that in Monarch and do the filtering and summaries on the exported version.  Not a huge amount of work but not terribly elegant all the same.  There's a possibility that our method of working, heavily reliant on Monarch, is going to be introduced to another office and so I'd like to keep everything as simple as possible - the fewer steps the better!

                                • Problems with a Summary
                                  Joe Berry

                                  I have had this issue before.  Grant is correct - it is the data.  More specifically it is the field used to join to the external table.  In my case, I had done almost everything you have already tested and did not find the answer.  I ended up using a different external table and all worked.  My tables were both created from a clinic information system that is SQL based.  I can't prove that was the cause, but that is my thought.  Their creation of external files, both for Excel and PDF, is not perfect; therefore, I use csv whenever possible.

                                    • Problems with a Summary
                                      Kamawell2 _

                                      I have had this issue before.  Grant is correct - it is the data.  More specifically it is the field used to join to the external table.  In my case, I had done almost everything you have already tested and did not find the answer.  I ended up using a different external table and all worked.  My tables were both created from a clinic information system that is SQL based.  I can't prove that was the cause, but that is my thought.  Their creation of external files, both for Excel and PDF, is not perfect; therefore, I use csv whenever possible.[/QUOTE]

                                       

                                      Thanks Joe.  I've tried quickly converting the lookup table to csv but I'm still getting the same error.  Do you know what it was about your new table that made the difference?

                                        • Problems with a Summary
                                          Joe Berry

                                          I do not know. 

                                          My guess is that the data in your look up table is the issue, more than likely in your field that is used to join the two tables.  Have you downloaded the utility in the support section, File Strip, that removes unprintable characters?  Might be worth a try to see if it has any impact on the look up table that is in CSV format.

                                            • Problems with a Summary
                                              Grant Perkins

                                              Andy,

                                               

                                              If you are stuck on this don't be surprised - I think most of us have seen something like this once or twice - thankfully it is rare though.

                                               

                                              If you can make a sample of the file contents available, assuming that confidentiality wouold not be breeched, etc., since it sounds like we need the real file not a re-hash of it, I would be happy to take a look to see if I can spot anything. Another pair of eyes sometimes helps in these situations.

                                               

                                              Other than that I suppose the most common thing is that one or more fields just don;t match even if they look like they do. So either some hidden characters or maybe a space which is not too evident thus making the fields mismatch. If you open the lookup table as a database in Monarch to the field definitions match? If you are linking on a combination field created from two of more individual fields it's always worth going back to first principles and checking data lengths and so on. If eyeballing the csv file use a system font or fixed font - at least something that is non-proportional. Stuff gets disguised in proportional fonts.

                                               

                                              If you has an odd mismatch in a few records I would not expect the lookup to fail except for those records. So an early failure on the lookup suggest something fundamental in the way the data formatting or type perhaps is being handled by some part of the process. However the only way you could prove that would be some form of step by step elimination of each of the fields in turn - unless of course they are all problems! Getting the field types and DATA sizes to match in both files and double checking that they do is an important part of the analysis of this sort of challenge and something that we can all find is very difficult to ensure - there is always the temptation so assume things are as they seem. I think we all do that from time to time - hence why I am pushing the point now.

                                               

                                              HTH.

                                               

                                               

                                               

                                              Grant

                                                • Problems with a Summary
                                                  Kamawell2 _

                                                  Hi Grant

                                                   

                                                  Thanks for the further insights.  Just had something rather odd happen (or at least it seems odd to me) and wondered if it sheds any light on what's going on here.

                                                   

                                                  Whilst thinking about you'd written, I was going through and starting again from scratch.  I've done this so many times now I wasn't thinking about it too hard, so when I got a different result I went back through and double-checked what seems to have happened.

                                                   

                                                   

                                                  The way I've been doing up 'til now:

                                                  Open database

                                                  Add external lookup

                                                  Define filter based on lookup field

                                                  Make summary that includes the filter

                                                  Internal error!

                                                  /LIST

                                                   

                                                  But if I do it this way:

                                                  Open database

                                                  Add external lookup

                                                  Make summary that includes lookup field using "Default Filter" but with no filters defined 

                                                  Define filter based on lookup field

                                                  Amend summary to use new filter

                                                  Success!

                                                  /LIST

                                                    • Problems with a Summary
                                                      Grant Perkins

                                                      Hi Andy,

                                                       

                                                      Well that is interesting. It's probably because the day (Today)  begins with an F. Of course that theory could be disproved if you have previously tried redefining the model on a different Friday ....  

                                                       

                                                      Grab the success and run with it would be my advice. On the other hand if you want to know why ... not sure. You could try comparing the Model definitions in the info screens (or my looking at the 2 xmod files side by side in notepad or similar). Maybe also just check that all the specifically active fields for the look up and filter are of the same type and size (Data Size).

                                                       

                                                      It certainly seems odd but potentially very specific to the data content and the way things are set up according to the order of events.

                                                       

                                                      What sort of filter have you defined?

                                                       

                                                      Whatever the reason it has to be a good way to start to end the working week!

                                                       

                                                      Have a great weekend.

                                                       

                                                       

                                                      Grant

                                                        • Problems with a Summary
                                                          Kamawell2 _

                                                          Grab the success and run with it would be my advice. /QUOTE

                                                           

                                                          Absolutely!  Right now as long as that method keeps working (because I've got a lot of reports that need this extra field added) I'm happy. 

                                                           

                                                          What sort of filter have you defined?[/QUOTE]

                                                          The filters are value-based, looking at the field populated by the external lookup. 

                                                           

                                                          Have a great weekend.[/QUOTE]

                                                          You too and thanks to everyone for their contributions.

                                                           

                                                          Andy