7 Replies Latest reply: May 15, 2014 10:01 AM by Data Kruncher RSS

    Filter bug?  Model corruption?

    Nancy10 _

      Monarch Pro 9.01

       

      I've built a fairly complex model and am now trying to filter data.  The filter is producing results that I believe are incorrect.  The model contains over 30,000 rows.  I am scrutinizing 18 rows to test the filter.

       

      Here are the 18 rows I am working with.

       

      COLBL3156365

      NIK313779111

      ETN430100003191

      NIK312628111

      COLSB4109610

      SOU73231076CRM

      BRI050663

      JOL88620XR

      BRI341625

      BRI341625

      BRA85004

      SOU90033004DSB

      BRI2011571

      5010134

      5010134

      2008991

      2008991

      SHAMROCK

       

      I want to filter the duplicates.  There are three of them, so the filtered list should contain 15 items.

       

      I created a filter as follows:

      Formula tab - empty

      Advanced tab - Select unique rows, select duplicate rows (one row only)

       

      Here are the results:

      COLBL3156365

      NIK313779111

      SOU73231076CRM

       

      This makes no sense!  So I did some experiments. 

       

      First I tried saving the model to a new name.  That did not help.

       

      Then I copied and pasted the 18 unfiltered rows from Monarch to an Excel spreadsheet.  I saved the spreadsheet.  Then I opened the Excel database with Monarch.  18 rows displayed.  I created the filter described above.  15 rows displayed. 

       

      So I went the next step.  I exported 30,000+ rows to Excel.  I opened this Excel database with Monarch.  30,000+ rows displayed.  I created the filter described above.  3 rows displayed:

       

      COLBL3156365

      NIK313779111

      SOU73231076CRM

       

      Am I bumping into some limit of Monarch?  I would expect Monarch to cope with a model consisting of 30,000 rows of plain data (no calculated fields, no lookups) and just one filter.

       

      Thanks for any help you can provide.

       

      Nancy

        • Filter bug?  Model corruption?
          Nick Osdale-Popa

          Hmmm.... I'm find it hard to duplicate the problem using 9.01:

           

          I took your sample data and pasted it into a text file, and then duplicated it so that there were 33,480 rows.

           

          I then created a filter as yours:

          Select rows according to their uniqueness with respect to the specified keys:

          x Unique Rows

          x Duplicated Rows: one row only

           

          It successfully showed only the 15 records.

           

          Perhaps the url=http://www.monarchforums.com/showthread.php?t=1470&highlight=engineMicrosoft Jet Engine KB patch[/url] is in order?

            • Filter bug?  Model corruption?
              Nancy10 _

              I'm now thinking that the problem is more complex than the way I was looking at it.  As you can imagine, I presented it very simply, but because the data and the model are complex, I now think I oversimplified.  I need to look at it again, and will get back to you.  I believe I know where the problem is, but I'm not sure I know how to correct it.

               

              Stay tuned --

               

              Nancy

                • Filter bug?  Model corruption?
                  Grant Perkins

                  Nancy,

                   

                  Like Nick I got the expected results.

                   

                  I'm wondering if you have a sort or something in there that is present in the data sample scenario but not in the raw data table and therefore present differently when only the filter is applied. Perhaps not that problem exactly but something akin to it.

                   

                  I'm curious to know what you find.

                   

                   

                  Grant

                    • Filter bug?  Model corruption?
                      Nancy10 _

                      Nick and Grant,

                       

                      I solved the problem I was having with filtering unique items. I needed to add another filter key. Seems pretty silly in hindsight. But that's so typical. 

                       

                      Thank you for your help.

                       

                      Nancy

                        • Filter bug?  Model corruption?
                          Grant Perkins

                          Nancy,

                           

                          Glad you got to the nub of the problem.

                           

                          I think we have all been in that position, often quite frequently in my case!

                           

                          I find the forum useful for generating ideas about where to look for solutions even if it does not offer an obvious direct answer. Seems this time it has provided the prompt you needed about where to look and what to do about it. Which is great.

                           

                          Have fun.

                           

                           

                          Grant

                            • Filter bug?  Model corruption?
                              fino _

                              Hi,

                              in realtion to this issue - please excuse the fact that I am now only looking at this issue - I too followed the same route and returned only 15 rows. However I would like to ask another question as to why in the formula tab of the filter definition there is a number 1. I deleted this and saved model and reopened the filter only thave the 1 return. I am sure that there is a logical reason for this and probably a simple one too - anyone put an end to my curiosity please?

                                • Filter bug?  Model corruption?
                                  Data Kruncher

                                  Hi fino,

                                   

                                  When using the "unique" options relating to advanced filters, Monarch will always assign a value of one to the expression, so that it will interpret the formula as a boolean "TRUE" when processing the records.

                                   

                                  This is specifically so that it will always[/B] send all /Bof the table records to the filter, so that the filter can move to the next stage, and determine which are unique, based on the rules that were defined as to what constitutes unique records.

                                   

                                  Clear as mud?