16 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Filter not running through entire report

    Hobbes _

      I have a model that runs over a report and then a filter that extracts the transactions for certain account numbers.  Albeit my reports are enormous (between 2 and 4.5 GB each), but my model and filter have worked on 4 out of the 6 reports.  The two it doesn't want to get through are not the largest reports I have.  For some reason it has decided to not work its way through the entire report the last several attempts I've made.  It will still extract information, but it's usually only for the top 200,000 lines or so.  Is there a setting or something I can look at or change to make it get through the entire report?  Thanks in advance for your help!!!  [img]tongue.gif[/img]

        • Filter not running through entire report
          Grant Perkins

          Hi Hobbes,

           

          Is this exactly the same model with exactly the same filter or the same filter in a different model or a different filter in a different model?

           

          Are you starting from a report or a database file?

           

          Are your reports or files local or on a network?

           

          Sounds strange. I have recently been running some filters against files with 2.8 million records and 30 fields per record with no signs of any anomalies.

           

           

          What is different about the 2 failing reports compared to the 4 successful ones? Does the failure always happen at the same place? Have you checked for any odd possibilities in the data that might affect the way your filter works?

           

          Out of interest which version of Monarch are you using?

           

          I don't have the answers but I hope the questions will help us get to a solution.

           

          Grant

           

           

            Originally posted by Hobbes:

          I have a model that runs over a report and then a filter that extracts the transactions for certain account numbers.  Albeit my reports are enormous (between 2 and 4.5 GB each), but my model and filter have worked on 4 out of the 6 reports.  The two it doesn't want to get through are not the largest reports I have.  For some reason it has decided to not work its way through the entire report the last several attempts I've made.  It will still extract information, but it's usually only for the top 200,000 lines or so.  Is there a setting or something I can look at or change to make it get through the entire report?  Thanks in advance for your help!!!    [img]tongue.gif[/img]   /b[/quote]

           

          [size="1"][ August 06, 2004, 03:44 PM: Message edited by: Grant Perkins ][/size]

          • Filter not running through entire report
            Hobbes _

            Hi Grant,

            I am running Monarch V7.01 Pro.  My reports are located on a 250GB external hard drive(connected with a USB 2.0 so that probably accounts for some of the sluggishness). 

            There is no difference in the reports I'm processing.  They are account logs and the only reason there is more than one report is because we couldn't put all of them in the same file, so it was split into 6 files.  They are text files that I open in Monarch, then I open the model, then I click on the table view where my filter runs automatically.  I use the exact same model and all of the reports are in the exact same format.  There is only a small difference in the size of the reports, but they were split fairly evenly.  My reports are an average of 30 million lines. 

            Everything in the environment is exactly the same, so I'm not sure why I'm just now running into problems.  I try to run the filter overnight (when my computer isn't touched). 

            There isn't a set spot that the filter stops, but I can tell that I'm not getting the records I need through cross referencing information already pulled.  I also do a search in the report to make sure the whole report shows up in Monarch (which it does)and to make sure those records are the same as all the others (which they always are). 

            I'm quite stumped on this one because I can't find any variables in the way I run the report, model, and filter. 

            I don't know if this helped, but perhaps something I said reflects something that has happened before to you or someone else.  Thank you so much for your help with this!

            • Filter not running through entire report
              Grant Perkins

              Originally posted by Hobbes:

              Hi Grant,

              <big snip>

               

              I don't know if this helped, but perhaps something I said reflects something that has happened before to you or someone else.  Thank you so much for your help with this! [/b][/quote]Well sort of! Sounds like you have checked all of the obvious stuff (unless there is something so obvious it is easily missed ..       ) which is all part of the process of elimination.

               

              Have you any way of knowing whether the process is stopping early (short elapsed time) or running a full process time but not selecting?

               

              Also as you are running unattended - does that apply for all the reports? Do you run every report every night? Is there anyhting that might kick in and interrupt the process?

               

              I also have 7.01 Pro. Sometimes I see error messages reporting 'Internal error' or something similar when running a large load or summary creation on a big file. But it is unusual to get a consistent problem. A re-run normally works OK - so I blame the PC or O/S  (XP in my case).

               

              Always the same 2 files or their latest versions?

               

              Just to drop back into dumb question mode for a minute, are you absolutely sure your filter is not working  - you're not expecting to see something that is not there, and should not be, by any chance?  I speak from experience - I own the rights to the T-shirt on that one!

                redface.gif[/img] 

               

              Grant

              • Filter not running through entire report
                Hobbes _

                OK, here's something strange I noticed when I ran the last report.  I got more records to pull up this time, but it's only pulling for one specific account number that I have the filter set to pull.  My filter looks for ten specific account numbers and the one it pulls is the eighth on the list.  I'm not even sure it's pulling all the records for that account, but that's the only account showing up.  I checked my filter again and there were no obvious problems.  I checked the sort and I don't have one set.  I checked the calculated fields (looking for anything odd here) and those didn't pose any obvious problems. 

                 

                I am sure that there are more records that need to be pulled because I did a manual search for a couple of them in the report and the model has them highlighted.  These are records that should be in the table view, but don't show up (in addition to all the transactions from the other nine accounts I am looking for).

                 

                All of the successful runs of the reports happen overnight.  I think there is enough time in my work day to get the model through the report with all of the interruptions that happen (email, etc.).  Since my reports are not running through a network (they're running off my hard drive), I can't see any good reason why something might happen overnight (when updates are run on the servers).

                 

                You mentioned that you had trouble with your O/S occasionally.  We are about to be upgraded from 2000 to XP.  Should I expect certain problems when running through XP?  So far I haven't received any O/S errors.  I did have an external hard drive error shortly after the first problems with running the report happened.  I had our tech department fix the problem, but they couldn't determine why it happened.  This is the only abnormal thing that I can think of that's happened, but it occurred after the errors/problems first surfaced (could be related though).  Anything else you think I should check into?

                Thanks!!!   [img]tongue.gif[/img]

                • Filter not running through entire report
                  Grant Perkins

                  All very odd.

                   

                  How is your filter setting the 10 values? Are you using  .IN. or perhaps a nested IF function?

                   

                  As far as XP and things are concerned I have no way of pinning anything down yet. However I was setting ap a new model against a file I had not previously doeone anything with yesterday (Tab delimited concatenation of 12 files plus a one line file for the column headers). Gives about 5.8 million records.

                   

                  The first attempt at a summary - 2 fields and a count - failed with 'Internal Error' after a couple of minutes processing and everything I tried after that also failed with the same error.

                   

                  After a reboot all was OK. I suspect some form of 'process leakage' or stack pointers getting corrupted.

                   

                  Now the thing is that I don't often re-boot the system and XP is very resilient and forgiving by comparison with, say, Win 98. But it is not perfect.

                   

                  I also experienced, before the re-boot as I recall, a partial read of the file that presented itdelf as a completed process. That sound more similar to the problem you are getting. I am reporting 12 years of data and the apparently successful pad stopped abiut 2/3 through the 11th year and missed the 12th year completely.

                   

                  Worked fine when I re-used the model against the same file after a reboot.

                   

                  I also use an external 'portable' disk bit only 20Gb or 30Gb capacity and using Firewire. I don't recall having any problems EXCEPT that Win XP Home Edition does not always present the disk as a device on an enquiry screen even though it has been recognised and is usable. It's more annoying than it is a problem.

                   

                  Other than that nothing consistently stands out and problems of that nature could be almost anything. I can never repeat them in a specific test - so am never ready for them on the rare occasions they arise!

                   

                  I think it may simply be a case of noting all circumstances and reviewing to see if any ne connections become apparent. Frustrating but something will stand out eventually.

                   

                  Grant

                   

                   

                  Originally posted by Hobbes:

                  OK, here's something strange I noticed when I ran the last report.  I got more records to pull up this time, but it's only pulling for one specific account number that I have the filter set to pull.  My filter looks for ten specific account numbers and the one it pulls is the eighth on the list.  I'm not even sure it's pulling all the records for that account, but that's the only account showing up.  I checked my filter again and there were no obvious problems.  I checked the sort and I don't have one set.  I checked the calculated fields (looking for anything odd here) and those didn't pose any obvious problems. 

                   

                  I am sure that there are more records that need to be pulled because I did a manual search for a couple of them in the report and the model has them highlighted.  These are records that should be in the table view, but don't show up (in addition to all the transactions from the other nine accounts I am looking for).

                   

                  All of the successful runs of the reports happen overnight.  I think there is enough time in my work day to get the model through the report with all of the interruptions that happen (email, etc.).  Since my reports are not running through a network (they're running off my hard drive), I can't see any good reason why something might happen overnight (when updates are run on the servers).

                   

                  You mentioned that you had trouble with your O/S occasionally.  We are about to be upgraded from 2000 to XP.  Should I expect certain problems when running through XP?  So far I haven't received any O/S errors.  I did have an external hard drive error shortly after the first problems with running the report happened.  I had our tech department fix the problem, but they couldn't determine why it happened.  This is the only abnormal thing that I can think of that's happened, but it occurred after the errors/problems first surfaced (could be related though).  Anything else you think I should check into?

                  Thanks!!!    [img]tongue.gif[/img]  /b[/quote]

                  • Filter not running through entire report
                    Hobbes _

                    Thank you for the explanation of the problem you had with XP!  I'll keep a lookout for problems after we transfer O/S.

                     

                    My filter uses .Or..  So, it would read "Account=12345 .Or. Account=23456 .Or. Account=34567 .Or Account=45678" etc.  Am I doing this the hard way?  I know the filters take a very long time to process their way though the report (which is why I run them overnight).  If I use .In. or a nested IF function, would that increase the speed or accuracy of my filter?  The filter seemed to work perfectly for the first four reports (as I originally mentioned), but would something else be more efficient?

                    Thanks!!!  [img]tongue.gif[/img]

                    • Filter not running through entire report
                      Nick Osdale-Popa

                      Instead of using .OR., you could try using .IN. :

                      [font="courier"]Account .IN. (12345, 23456, 34567, 45678, etc)[/font][/quote]Not sure if the processing for this is faster or not though.

                       

                      Edit: Just fully[/i] read the rest of your post and see that you have tried .IN.   smile.gif[/img] 

                       

                      .IN. is just easier to edit, IMO, and you only need to refer to the field once.

                       

                      [size="1"][ August 09, 2004, 02:56 PM: Message edited by: Nick Osdale-Popa ][/size]

                      • Filter not running through entire report
                        Hobbes _

                        Hi Nick!  Thank you for your suggestion!

                         

                        I tried using .In., but I keep getting the error 'Operand Types'.  Also, some of my account numbers have letters in them and I got an error message saying 'Invalid terms'.  I put quotation marks around the values and that seemed to fix the problem.  Did I do that correctly?  Do you have any suggestions why I would be getting the 'Operand Type' error?

                        Thanks in advance for your help!!!

                        • Filter not running through entire report
                          Hobbes _

                          I'm not sure what I did differently, but I got the .In. to work.  I think it was because I only had quotation marks around the account numbers with letters.  Thanks again Nick for writing out the code...that is incredibly helpful when you do that!    [img]tongue.gif[/img]

                          • Filter not running through entire report
                            Grant Perkins

                            The "operand types" error relates to using a numeric function on a field defined as alpha or vice versa.

                             

                            Some functions are specific to either numeric or alpha only. It is possible to perform alpha functions on numeric fields by using other functions to convert the numeric string to alpha, run the function and then convert the result back again. This can all be achieved within a single formula.

                             

                            Fields defined as Alpha would need quotes around the comparison data. Data comparisons for Numeric fields should not have quotes. It is easy to let things slip through from time to time! Especially if you have what looks like a numeric field but that has been defined as alpha to suit just a few records.

                             

                            However the .IN. operator, as far as I can tell, does not support embedded formulae though if anyone can tell me otherwise I would be delighted!

                             

                            Grant

                             

                             

                            Originally posted by Hobbes:

                            I'm not sure what I did differently, but I got the .In. to work.  I think it was because I only had quotation marks around the account numbers with letters.  Thanks again Nick for writing out the code...that is incredibly helpful when you do that!     [img]tongue.gif[/img]  /b[/quote]

                            • Filter not running through entire report
                              Hobbes _

                              So I changed my filter operation to .In. instead of .Or. and the problem I was having with the filter not running through the entire report seems to have been fixed.  Perhaps the report was too big for the .Or. operation, don't know.  The number of records produced was closer to what I was expecting, but an audit of the information will have to wait until tomorrow because my finger slipped off the mouse as I was going to export the data and accidentally closed the report and the model (  :eek:  ).  This will be a good test to see if the .In. operation can produce the same number of records the second time around.  Thank you all again for all of your help with this!!!   [img]tongue.gif[/img]

                              • Filter not running through entire report
                                Hobbes _

                                So I thought I had solved the filter not running through the entire report problem by changing the .Or. function to .In., but in fact the problem is back.  Does anyone have any idea what else I might look at or change to try and get this to work?

                                  tongue.gif[/img] 

                                Also, with this filter problem, I am finding it difficult to make my processing stable.  I am developing this process for a group of people to use, but at this point I'm not comfortable turning over this project to them.  Is there any way to help make things more reliable with Monarch?  It works great on small reports, but the larger ones are giving me grief.  Any suggestions???  :confused: 

                                 

                                Thank you!!  [img]smile.gif[/img]

                                • Filter not running through entire report
                                  Hobbes _

                                  Thank you Gareth for helping me find alternatives to solve my problem!!!

                                   

                                  It was determined that the size of the reports were the problem and I didn’t want to split them if I could avoid it.  After working with Gareth, he came up with the following solution.  Instead of opening Monarch, opening my report, opening my model, and clicking the table view to enable the filter, I started using the command line to run reports.  I went to the start menu on my desktop, clicked Run, and typed in my report, model, and export information in the following format:  (how to run from the command line is also in the help files)

                                   

                                  Monarch /rpt:C:ReportsReport1.txt /mod:C:MonarchModelsModel1.mod / exp:C:MonarchExportsDataExport1.xls /T

                                   

                                  The T is something I pulled off the help file (not exactly sure I understand what it does, but it doesn’t seem to hurt anything to have it there).

                                   

                                  Reports that were taking me over a day to run now complete their cycle in just over an hour.  This method is excellent for extremely large reports, but for simplicity and ease, I have a much smaller test file I use when changing my model and filter.

                                  I hope this helps someone else out in the future.    tongue.gif[/img] 

                                  Hobbes

                                  • Filter not running through entire report
                                    Bruce _

                                    Hobbes

                                     

                                    /T opens Monarch in table window.

                                     

                                    You can give your self a short cut with the special command lines Gareth gave you to avoid re-typing every time.

                                     

                                    Glad you got your gremlin fixed.

                                    • Filter not running through entire report
                                      Hobbes _

                                      Hi Bruce (or anyone else with an answer)  tongue.gif[/img]  ,

                                       

                                      Can you tell me how to create the shortcut to run the command line above?  Will this be a shortcut on my desktop?

                                       

                                      Thanks!!

                                      • Filter not running through entire report
                                        Grant Perkins

                                        Put the command line in a file with a .bat extension. e.g. report.bat or whatever name you prefer but keep the .bat part.

                                         

                                        If you want you can then use Windows to make a shortcut to the file and leave that on your desktop. Clicking on the shortcut or the file should then run the command. (Assuming you are running Windows XP or 2000. If you are not then you may need to put a command in the file to run the Autoscript program first.).

                                         

                                        I hope this helps.

                                         

                                         

                                        Grant

                                         

                                         

                                        Originally posted by Hobbes:

                                        Hi Bruce (or anyone else with an answer)   tongue.gif[/img]   ,

                                         

                                        Can you tell me how to create the shortcut to run the command line above?  Will this be a shortcut on my desktop?

                                         

                                        Thanks!! [/b][/quote]