5 Replies Latest reply: May 15, 2014 9:52 AM by Mike Urbonas RSS

    Row Filters

    KarenG _

      Wonder if anyone can help.  i need to be able to extract/filter a specified % of rows from a table.  i thought that the best way to do it

      was to filter the rows using Row(no), but apparently this isnt allowed! i want to be able to use the mid 90% of a block of data - has anyone any idea?

        • Row Filters
          Mike Urbonas

          Try filtering using recno() instead.

           

          Filtering based on rowno() is not possible because if you were to apply a filter based on row numbers, the remaining data's row numbers will then change, and perhaps then meet the original filtering criteria, and so on and so on... Recno() values are based on each data row's unique record number which will change like a row number can, so filtering based on recno should work for you.

           

          Also, take a look at the Filters - Advanced tab (Monarch 7 and above, I believe). This lets you filter the first x number of rows from your table which meet any filter expressions and duplicate handling you have specified.

          • Row Filters
            KarenG _

            Mike,

             

            This doesnt work because the table has already been sorted on a specific column and i now want to eliminate the lower 5% and upper 5% of the table. The Rec(no) uses the original record row numbers, not the ones after it has been sorted.

            There must be a way!!

            • Row Filters
              Grant Perkins

              Karen,

               

              A functional if inelegant option would be to export the newly sorted table to a new report or database and then use that as the basis of you top and bottom trim.

               

              Is the 5% either end based on a number of records relative to the total number of records or somehow the value of the field by which the data has been sorted? If the latter is there any potential filtering by value, perhaps as some sort of Summary?

               

              Just thinking aloud.

               

              Grant

              • Row Filters
                KarenG _

                thanks Grant, i'll give it a go

                • Row Filters
                  Mike Urbonas

                  I have another idea, which involves use of the Summary window.  This may prove to be at least satisfactory for you.

                   

                  Go into the Monarch Summary, click Edit -> Summaries... then click New...

                   

                  Now enter all of the fields from your table in the order you want them to appear.  If you have never used Monarch summaries before, click on a field and then click on the "Key" button.  Important: you will need to select the field you want to sort on as your first field.

                   

                  Now click on the field "count" (a special Summary selection which will count values in your summary as you wish to).  Then click on "Measure".  Now "count" should appear in the Measure portion of the window.  Double-click on that "count".

                   

                  You should now be able to click on the "Calculation" tab.  Currently the "Count" button will be selected.  Instead, you will now select "Cumulative Percent of Total".  Click OK through all windows to get back to your Monarch summary.

                   

                  What you should now have is a view of your data within the summary, sorted by your first selected field, along with a new summary calculation showing each row number as a percent of the total number of rows.  You now at least know which rows of data you want to exclude from your analysis (in this case, all rows with a cumulative percent of total of 5% or less, or 95% and more).  Using this calculation, you can now highlight the rows you now know you want to export to Excel.

                   

                  To do what you are asking requires Monarch to "know" how many rows are in your table, and I think this is the only way to do that!

                   

                  Did this help?