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

    Bug where filter on existing model suddenly turns up no results?

    MonUserCJ _

      Hello all,

       

      I have been dealing with a frustrating problem where I open an existing project and the default filter, which previously returned many rows, returns no rows.

       

      One important detail is that I'm opening up about 6 sizeable CSV files at once (maybe 8 MB each). It's weird because the filter usually returns about 400,000 rows, but when I open it up it returns no rows.

       

      If I switch to no filter, the rows appear. If I switch to other filters than the default ones, the rows usually appear although sometimes not.

       

      The filter is very simple. It says that one character field must be 5 characters in length.

       

      The other condition of the filter is that another field must contain a period. Thus:

       

      InStr(".", Address)<>0

       

      I kind of wonder if it's the way I'm specifying this condition.

       

      By manually reconstructing the filter as a composite filter, I was able to get the rows to reappear, but then that filter seemed to stop working. I tried re-specifying the database files (i.e. going to File, Database...). That didn't work.

       

      In general, it seems kind of temperamental. Has anyone else experienced this issue? I would appreciate any anecdotes people can contribute, as well as insights into why it's happening and advice on avoiding it. Thanks.

        • Bug where filter on existing model suddenly turns up no results?
          Olly Bond

          Hello CJ,

           

          This can happen when you combine lots of joins (lookups) with filters. You're asking Monarch to do a lot of work at once, and if the filter is applied to a field that only exists in a lookup table, you're telling Monarch to:

           

          a. open the original database, take all records

          b. make the first lookup, bringing in all data to match all records

          c. make the second, etc

          d. make the third,

          e. make the fourth,

          f. make the fifth,

          g. make the sixth

          h. evaluate the filter expression against the whole set.

           

          Generally, for summary outputs, Monarch is very good at going through the data set with the right order of operations to get you what you need, but, especially if the fields on which you are joining one of the subsequent lookups are themselves imported in a previous lookup, then you can find occasional behaviour where Monarch trips over its own shoelaces and doesn't get you all your data.

           

          The fix is to break the model down into six different models - one to open the first file and apply any filter which just depends on the fields in this, then export this as a local .mdb Access table. The second model opens this table, and makes the first external lookup, then applies any filter that just uses the fields in this, and again, export the filtered table to a different local Access table. Repeat, adding one lookup and making any possible filter at each stage. Now you can run the six projects one after the other, using DataPump if you want logging and error-reporting and all those nice things, or on the desktop with a batch file or a script, and you should have an error-free routine to join your data.

           

          Best wishes,

           

          Olly

            • Bug where filter on existing model suddenly turns up no results?
              Grant Perkins

              Are the 6 files all version of the same thing?

               

              If so, if you open the files one at a time does the filter work?

               

              Something to eliminate first ....  at the very basic level I have seen what appeared to be random results that were "other than expected" which turned out to be related to the size of the internal worktable being created. Monarch's internal database engine is limited (per MS Access) to 1Gb and in past version did not advise that it had "finished" loading the data because the working file was full rather than because it had reached the end of the records it could extract from the source.

               

              This sometime gave unpredictable results especailly if multiple files were open since they were not always read in the same order. Thus record that you would expect to see, based on the filter, may never actually get to the workspace database to be filtered.

               

              6 x 8Mb files is quite a lot although there is some influence on data asize depending on the number and size of fields you are extracting and creating as part of the process.

               

              Rahther than specualte further about a way forward for such a problem I'll wait to see whether this may in fact apply to your case. It might not. You may have something completely different going on.

               

              Is this still V9 as per your profile?

               

               

              Grant

            • Bug where filter on existing model suddenly turns up no results?
              sookie49 _

              I have had this problem with Monarch Pro since version 7 or 8.  Support has only been able to tell me that they have recorded the problem and they are working on it.

               

              Try this method which usually works for me.

              First insure that no filter is being applied.

              Then go to  >> Data >> External Lookups >>

              Click the "Refresh" button then apply your filter.

               

              For some reason this method doesn't seem to work if you use the "Refresh Database" under  >> File tab

               

              Regards,

              Jim