12 Replies Latest reply: May 15, 2014 10:09 AM by sookie49 _ RSS

    Filter

    Hin _

      I set up a filter to display only records with an amount in a column.  When I turned on the filter, no records was displayed.  When I turned off the filter, I could clearly see some records with an amount in the column used for the filter.  The field property shows clearly that the amount column is numeric.

       

      I did  a test by creating a calculated field equal to 1 times the amount column.  I then used the calculated field in the filter, and it worked!  I wonder why that is the case.  Any advice will be much appreciated.

        • Filter
          Nick Osdale-Popa

          Are you checking to see if there is an amount as to no amount (ie no data) or that the amount is not equal to zero?

          If the former, your filter should look like this:

          .not.IsNull()

           

          If the latter, your filter should look like this:

          <>0

           

          Let us know if either of those work and what you actually have as your filter criteria.

            • Filter
              Hin _

              The filter is like this:

               

              <>0

               

              It is even more intriguing that I copied the project with the model to my colleague's computer which had the same Monarch version and update as mine and the filter worked fine.  What do you make of that?

                • Filter
                  Bill Watson

                  I sometimes have the same issue with filter's not behaving while I am working on the model build. This especially happens where you have complex models with a large dataset, and large external lookups.

                   

                  First thing I do is to refresh external lookups.

                   

                  If that does not resolve the issue, then I save my work and reopen the model which usually does the trick.

                   

                  I am unsure if it is a caching issue that is causing a bottleneck when monarch works with these complex models - but closing and reopening seems to flush out any issues. This would be supported by the fact that when another user opens the model fresh they do not see the problem.

                   

                  The funny thing is this filter issue only seems to occur to me when I am actively changing calculations in models. It does not happen when the model/project is run via datapump or scripting.

                    • Filter
                      adonis _

                      did you check the following:

                      1) make sure no leading spaces before the 0?

                      2) make sure there is no decimal places after the 0?

                      sometimes that is a help.

                      sometimes what i do in adance is make sure the file i am dealing does not have any spaces to ensure and reduce noise level, not to mention the 0.0001 or some other issues that arise, hopefully you found your issue, just saw this and thought to check.

                    • Filter
                      sookie49 _

                      It is even more intriguing that I copied the project with the model to my colleague's computer which had the same Monarch version and update as mine and the filter worked fine. What do you make of that?[/I]

                       

                      I had this same experience where it would work on someone else computer.  I was able to recreate this on one other computer which turned out to be the same make and model as my computer.   I'm using a Dell Optiplex 745.

                        • Filter
                          adonis _

                          hmmm I do not use 10.5 but it is my understanding that pertains to using MS Excel and other functionality.  I only use 10.01 because i do not have the latest MS Word 2007 as my standard is 2003 at the moment.  Can you check to see what version you have and what your colleague has for starters?

                           

                          Minimum System Requirements

                          • Windows XP Home/Professional SP2, Windows 2003 Server SP2, Windows Vista SP1, Windows 2008 Server, Windows 7

                          • 2 GB RAM

                          • 200 MB disk space for installation

                          • Pentimum 4 CPU

                          • 16-bit color display

                          • Internet Explorer 6 SP1

                          • .NET Framework 3.0 SP1

                          Excel 2007 required for adding source report(s) and digital signatures to Excel files.

                            • Filter
                              dmorse _

                              Hin, this is a continuing problem with Monarch. You'll find other threads on this forum about it, mostly from frustrated users like me. I don't come here often, but I do check back from time to time to see if there has been a breakthrough on this issue ... apparently not.

                               

                              I currently use v10.5, but have been using Monarch for many years ... since v4, I think. I use it almost every day, and would be lost without it. We are preparing for an implementation of Monarch Data Pump early next year. Having said that, the issue you raise is a HUGE problem that Datawatch seems to ignore (are you listening, moderator?). I work in a fairly large organization where the datasets are large, but not huge (up to a million records at the high end), and they can be in MDB, SQL, DBF, or delimited text. The behavior is the same with all of them. Our audit department is starting to view Monarch with suspicion because of its behavior in this regard, and if they ultimately become disenamored enough with the program to blacklist it, I'm dead. I couldn't get my job done.

                               

                              Like you, Bill Watson, I have found that it is most prevalent "where you have complex models with a large dataset, and large external lookups." /I And like you, I have also found that saving and reopening sometimes helps. But that doesn't always help, and the problem occurs with me regardless of whether I'm actively changing a model or not.

                               

                              At one point I was convinced that it was something in my operating environment upon which Monarch depends ... the Microsoft jet database driver or something. Getting different results on different computers seems to point to that. But even switching machines doesn't cure the problem consistently. I have a hunch that it has something to do with null values in the datasets when external lookups are used, as I have noticed that null-sensitive filters, or filters that reference a calculated field that is null-sensitive, seem to be the most prone to the issue. I'm not prepared to say, however, that that is always the case.

                               

                              Some work-arounds that I routinely try:

                              1) Use a compound filter. This almost always works. For example, instead of a single filter such as isnull(FieldA)[/B], define another one that wouldn't filter anything from your data, like FieldB <> 0,[/B] then combine the two into a compound filter.

                              2) Trim your text fields. Instead of FieldC = "Elephant"[/B], use Trim(FieldC) = "Elephant".[/B] Even if there are no trailing spaces on your text field, invoking the formula seems to help.

                              3) Modify the formula to an alternate form. For example, if isnull(FieldA)[/B] doesn't work, try isnull(FieldA) = 1[/B] or isnull(FieldA) <> 0.[/B] Theoretically, all three should yield the same results, but I know from experience that that isn't always true. Another example: FieldF <> 2[/B] is synonymous with FieldF .notin. (2)[/B], but one may work when the other one won't. The next time you use the model, you might have to fiddle with it again, which is even more frustrating, but you can at least get results when you're up against a deadline.

                              4) If all else fails, assemble your raw data, with all external lookups and without filters, then export everything to an intermediate table (Excel or whatever). Close everything, then open that table as a database and apply your filters. It has been my experience that without any external lookups, you're much less prone to have problems.

                               

                              These steps take time, of course, and you shouldn't have[/I] to put up with this fiddling ... this is the part that is frustrating to me. If someone out there has a permanent and definitive solution to this, I would appreciate it if you would contact me via this forum. I have spoken to a number of Monarch users who say something like, "Oh, yeah, that happens to me, too, but I just do/did so-and-so to get around it." I'm not satisfied with this answer.

                                • Filter
                                  Olly Bond

                                  Hello dmorse, hello everyone,

                                   

                                  Thanks for that excellent post - the idea of compound filters is an excellent one. You could define a filter of Recno()>0 which catches everything, and might force Monarch to run through every record looking for the Recno, and compound that with whatever expression you need.

                                   

                                  I've also found that timing issues seem to cause most problems - external lookups to network sources are often the cause - and I tend to join each external lookup in a single project, and output a local MDB file each time. This way lets you export a summary counting how many null lookups occur at each stage, so you have an audit check as well.

                                   

                                  HTH,

                                   

                                  Olly

                                    • Filter
                                      sookie49 _

                                      The one way I've found to get the filter to work properly is to switch to no filter, refresh external lookups, then switch to the desired filter.  This seems to work until you or Monarch cause another refresh to occur.

                                    • Filter
                                      Gareth Horton

                                      Hi DMorse,

                                       

                                      We have heard reports of this issue, but it is impossible to track down without a reproducible example, which have never had in house.

                                       

                                      I am happy to work with you on nailing this, if you can provide me with a working (or rather, failing) example that can be reproduced here at Datawatch (so no references to SQL databases etc - file based DBs like Access. Excel etc are OK).  We handle a lot of sensitive data, so we can certainly try and make any accommodations in that area.

                                       

                                      Send me a private message with your e-mail address and we can take it from there.

                                       

                                      Thanks

                                       

                                      Gareth

                                       

                                      Hin, this is a continuing problem with Monarch. You'll find other threads on this forum about it, mostly from frustrated users like me. I don't come here often, but I do check back from time to time to see if there has been a breakthrough on this issue ... apparently not.

                                       

                                      I currently use v10.5, but have been using Monarch for many years ... since v4, I think. I use it almost every day, and would be lost without it. We are preparing for an implementation of Monarch Data Pump early next year. Having said that, the issue you raise is a HUGE problem that Datawatch seems to ignore (are you listening, moderator?). I work in a fairly large organization where the datasets are large, but not huge (up to a million records at the high end), and they can be in MDB, SQL, DBF, or delimited text. The behavior is the same with all of them. Our audit department is starting to view Monarch with suspicion because of its behavior in this regard, and if they ultimately become disenamored enough with the program to blacklist it, I'm dead. I couldn't get my job done.

                                       

                                      Like you, Bill Watson, I have found that it is most prevalent "where you have complex models with a large dataset, and large external lookups." /I And like you, I have also found that saving and reopening sometimes helps. But that doesn't always help, and the problem occurs with me regardless of whether I'm actively changing a model or not.

                                       

                                      At one point I was convinced that it was something in my operating environment upon which Monarch depends ... the Microsoft jet database driver or something. Getting different results on different computers seems to point to that. But even switching machines doesn't cure the problem consistently. I have a hunch that it has something to do with null values in the datasets when external lookups are used, as I have noticed that null-sensitive filters, or filters that reference a calculated field that is null-sensitive, seem to be the most prone to the issue. I'm not prepared to say, however, that that is always the case.

                                       

                                      Some work-arounds that I routinely try:

                                      1) Use a compound filter. This almost always works. For example, instead of a single filter such as isnull(FieldA)[/B], define another one that wouldn't filter anything from your data, like FieldB <> 0,[/B] then combine the two into a compound filter.

                                      2) Trim your text fields. Instead of FieldC = "Elephant"[/B], use Trim(FieldC) = "Elephant".[/B] Even if there are no trailing spaces on your text field, invoking the formula seems to help.

                                      3) Modify the formula to an alternate form. For example, if isnull(FieldA)[/B] doesn't work, try isnull(FieldA) = 1[/B] or isnull(FieldA) <> 0.[/B] Theoretically, all three should yield the same results, but I know from experience that that isn't always true. Another example: FieldF <> 2[/B] is synonymous with FieldF .notin. (2)[/B], but one may work when the other one won't. The next time you use the model, you might have to fiddle with it again, which is even more frustrating, but you can at least get results when you're up against a deadline.

                                      4) If all else fails, assemble your raw data, with all external lookups and without filters, then export everything to an intermediate table (Excel or whatever). Close everything, then open that table as a database and apply your filters. It has been my experience that without any external lookups, you're much less prone to have problems.

                                       

                                      These steps take time, of course, and you shouldn't have[/I] to put up with this fiddling ... this is the part that is frustrating to me. If someone out there has a permanent and definitive solution to this, I would appreciate it if you would contact me via this forum. I have spoken to a number of Monarch users who say something like, "Oh, yeah, that happens to me, too, but I just do/did so-and-so to get around it." I'm not satisfied with this answer. :(/QUOTE

                                        • Filter
                                          elginreigner _

                                          Maybe I'm misunderstanding the other users issue but what I get from the posts are the lookups are the issues. How can Datawatch accurately fix the issue without the same lookups?

                                           

                                          I personally have seen this issue arise from lookups only.

                                • Filter
                                  sookie49 _

                                  I have this problem when I try to filter on a value that was obtained in an external look-up.  Sometimes it filters properly and other times it filters out everything.  I replace null values with default values as part of the external look up process. I was told it might be a memory problem so I doubled my memory to 4 GB and it didn't help.  I have had this problem in ver. 9 Pro and now in ver 10.5 Pro.