6 Replies Latest reply: May 15, 2014 10:04 AM by rorselli _ RSS

    trouble when filtering a database

    rorselli _

      Hi,

       

      I'm in a migration process from 'monarch 5 pro' to 'monarch 9 pro'

       

      I'm facing trouble when trying to filter some registers in a .xls file opened as database in the monarch 9 pro.

       

      this file opens normally, I can see all the registers and work to create calculated fields.

       

      In the sequence, I bring some other collumns through external lookup (joint). It works properly and the result is some fields with information and some with the (Null) value, where I could find no match.

       

      The next step (Where I'm having trouble) I try to filter using:

       

      .not. IsNull (Field_X)

       

      Being 'Field_X' a field brought in the lookup, where I want to discard the register where I have no match in the joint.

       

      When applied this filter returns a empty table. There is no apparent reason for monarch to do so.

       

      Does anyone can help me finding what is wrong? Any similar case?

       

      tks

       

      Rui Orselli

       

      P.S. The above cited processes was working properly in monarch 5 pro.

        • trouble when filtering a database
          Olly Bond

          Hello rorselli,

           

          Perhaps changing the filter expression to:

           

          IsNull(FieldX)=1 (to return only the null rows), or

           

          IsNull(FieldX)=0 (to return only the non-null rows)

           

          would do the trick?

           

          Best wishes,

           

          Olly

            • trouble when filtering a database
              rorselli _

              actually I've tryed  many ways of doing that, including calculating a field with values 0 / 1 , and defining a filter over these values.

               

              that way was the closest I got to a working model, but when the file was reopened (through a xprj) I ended with a empty table...

               

              but it gets stranger...

              on the sequence I set to no filter, then I can see all registers as expected.

               

              Then, I reselect the filter ( that has just resulted in a empty table... ) and I have the correctly filtered registers only...

               

              Can it be any configuration problem?

               

              tks for the help

               

              Rui

                • trouble when filtering a database
                  Olly Bond

                  Hello rorselli,

                   

                  I've replied to your post in another thread - in case you're using filters at the database import stage - suggesting a two project solution.

                   

                  The batch file generator I mentioned is at www.********************[/url] - DataKruncher's excellent site.

                   

                  Best wishes,

                   

                  Olly

                    • trouble when filtering a database
                      rorselli _

                      I've found a solution for my problem, i think it can help the others.

                       

                      when editing the filter for the version 9.01 of monarch, there are 3 tabs:

                      formula / advanced / test

                       

                      on the formula tab I did no modifications to the existing filter that is causing problem

                       

                      on the advanced tab you do the following

                       

                      -select the option:

                      'select rows according to their uniqueness with respect to the specified keys'

                       

                      -then check only the option:

                      'Duplicated rows'

                       

                      -in the Duplicated rows select:

                      'all duplicated rows'

                       

                      in the combo list in the right side, there is a list of fields that exists on the model called:

                      'Specify Keys'

                       

                      Here is the trick, you now have to select a field in the list where every value is the same for all register.

                      In my case I've choosen the 'Data' field that for my table was constant in every row

                      (sugestions for this step, create a calculated field with a constant value and use it!)

                       

                      Now I have a perfect functional filter that accepts modifications and behave as expected

                       

                      I hope I can help somebody

                        • trouble when filtering a database
                          Olly Bond

                          Hello rorselli,

                           

                          That's a really handy trick - it's not expected behaviour from Monarch, but it's nice to have found a workaround. Applying a "dummy" duplicate handling rule seems to force Monarch to think about the filter properly. Please could you confirm that this is for a filter applied to a database import or external lookup, as opposed to a normal filter within a table?

                           

                          Best wishes

                           

                          Olly