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

    How to eliminate the rows which has specific text or numbers

    rdbabu _



      Can you please let me know the options to use to eliminate a row which contains pecific text or numbers?



        • How to eliminate the rows which has specific text or numbers
          Data Kruncher

          Depending upon your input source (report or database), you may have the option of building your templates to use the NOT toggle to specifically exclude lines which have a certain character in a given position, therefore avoiding the problem in the first place.


          However, experience says that those situations are fairly rare.


          It's more likely that you have success using a filter in the table window to exclude records with fields that have the values that you don't want to include.


          So if your data had a Region field, and you wanted East, West, and South, but not North, then your filter formula would be:



          As well, you can exclude values, Sales <> 999.98, or SKU <> "A123".


          Taking it one step further, you can build filters that check multiple fields by using the Boolean operators .And., .Or., and .Not., such as:

          Region <> "North" .And. Sales <> 999.98[/CODE]


          Check out compound filters as well, which combine the rules built into multiple filters that have been previously defined.


          Also note that any summaries you build, or may have already built, may be affected by the filter currently in use.


          You can also take advantage of filters with project exports, so that Monarch applies each filter defined in the model sequentially when exporting data.


          The possibilities with filters are endless.



            • How to eliminate the rows which has specific text or numbers
              barnold _

              What about filtering out lines that contain a substring, for instance a line that has "Company: xxxxxxxxxx Desc: AF QJ ALLT"; where I want to get rid of lines that contain "ALLT" (and ALLT may be in a variable position, and is only one of the values I want to exclude from the result set).

                • How to eliminate the rows which has specific text or numbers
                  Data Kruncher

                  Welcome to the forum barnold.


                  Provided that your entire string "Company: xxxxxxxxxx Desc: AF QJ ALLT" is the value of, say, MyField, then I'd use the Instr() function in a filter with this sort of structure:

                  .Not. (

                  Instr("ALLT",MyField)>0  .Or.

                  Instr("QJ",MyField)>0 .Or.




                  Instr() returns the numeric position of "this text" within ThisField. So if ThisField had a value of "Test this text", then Instr("this text",ThisField) would return a value of six. If ThisField did not contain text matching "this text", then the function would return a zero value.


                  Using the .Not. operator along with >0 conditions allows you to easily add more lines for other exclusion strings, like "Kruncher" above. Breaking it up in the editor box so that each test appears on its own line allows for easier review of the conditions later on.