6 Replies Latest reply: May 15, 2014 9:59 AM by Dianne Hardin RSS

    FILTERING

    Dianne Hardin

      I'm trying to create a filter on a field where I can filter out, i.e.,  -MA or -PB.  How can I write this?  Please be specific because I am somewhat new to filtering.

        • FILTERING
          Dianne Hardin

          I want to remove the entire field.

          • FILTERING
            Dianne Hardin

            The name of the field is WARD AT DISCHG.

             

            Here is a sample of the ward names

             

            WARD AT DISCHG

            2A MED-MA

            3B MED-MA

            3B MED-MA

             

            52S1 SCI-

            23H MED-M

            3B MED-MA

            23H MED-M

            7-N MED-J

            7-N MED-J

            3B MED-MA

             

            If any of the wards in this field have a suffix of either -MA, -PB, -M or -P, I don't want that row on my report.  I do want the rows that have a dash with nothing following and -J.

             

            I'm still pretty new at Monarch and have learned the little that I know through the user manual that came with the software.  I usually experiment and can manage to make a great Model.  I've run across this before where I want to eliminate a field that contains something.  This time I finally threw in the towel and asked the experts.  Thank you so much for your time and patience.

            • FILTERING
              Grant Perkins

              Hi Dianne,

               

              You have a couple of options for direct filtering. They would be the same if you used an intermediate 'working' field for filtering purposes. The choice is entirely open and to me depends upon how complex the selections may get and whether multiple combinations might be required at some point.

               

              If multiples I would probably consider calculated fields to identify the criteria of the data that will allow me to folter so that I can more easily combine them into compound filters (I am assuming you have Version 8) later.

               

              If it will remain simple, no real benefit to doing that.

               

              You can choose an inclusive or exclusive filter for your sample. Which kind of filter depends on the balance of codes I reckon. The list to include or exclude would ideally, be the shorter list. So if you have 20 codes and only want two of them, make an .IN. selection.  If you only want to exclude two of them make a .NOTIN. selection.

               

              RSPLIT(,2,"-",1) .NotIn.("ma","PB", "m","p")

               

              RSPLIT(,2,"-",1) .In.(" ", "j")

               

              You could also use the LSPLIT function in this case just as readily. Or a number of other similar options if the requirement was not quite a clear cut as it is here - the "-" really helps the definition.

               

              The .IN. OPERATOR in a formula is very powerful BUT you may need to use it carefully  when selecting using codes which are quite similar. For example if you wanted to select M but not MA, or something similar to that, the results could, sometimes, be a little less obvious mainly due to the way such codes might have been entered in the first place.

               

              In the current case, as far as the sample is concerned, you could be flexible.

               

              RSPLIT(,2,"-",1) .NotIn.("m","P")

               

              seems to work fine.

               

              HTH.

               

              Grant

               

              [size="1"][ June 19, 2006, 04:28 PM: Message edited by: Grant Perkins ][/size]

              • FILTERING
                Dianne Hardin

                Wow oh Wow, this worked.  I even cheated and copied/pasted.  I did print this entire dialogue so I can now study your logic so I can understand it.  I hate to just do something because someone said that is the way it's done.  I need to understand how and why.  I feel like I have graduated to another level in Monarch.  By the way, I am using v8.

                 

                Thank you so very much for this help.

                • FILTERING
                  Grant Perkins

                  Hi Dianne,

                   

                  When I was starting out with Monarch I found it was things like this that that gave me a boost to get to the next level of understanding and encouraged me to try more and more experiments.

                   

                  The formula has two components really.

                   

                  One part is the filter and this hinges on the use of the .In. or .NotIn. operators which are useful in this case. Without those there are other, longer, ways of getting the same result. Users of early versions of Monarch will not have access to the .In. and .NotIn. operators.

                   

                  The other part is the separation of the key part of the code (from the data string) which allows you to make the filter selection. This is the Rsplit function. with it's sibling, LSPLIT. Basically LSPLIT is the same but working in the opposite direction on a string of data, so one just picks whichever is the better one to use under the circumstances.

                   

                  If you think of this as a 2 operation process - the SPLIT to create a field to use for filtering and then the formula to make the filter happen - it might be easier to see the component parts. And I would recommend exactly that aproach when first using this sort of functionality as it makes the steps easier to understand and test individually.

                   

                  Once both parts work it becomes a simple matter to make the whole thing work in one formula.

                   

                  So the Split would have been used to create a new field to work with for the filter.

                   

                  RSPLIT(,2,"-",1)

                   

                  Lets say we called this field WaD_Suffix and it it now has been added to the table.

                   

                  The filter would then be;

                   

                  WaD_Suffix .NotIn.("ma","PB", "m","p")

                   

                  or

                   

                  WaD_Suffix .NotIn.("m","P")

                   

                  or

                   

                  WaD_Suffix .In.(" ", "j")

                   

                  If I thought I might need to filter on this AND something else at the same time I would probably use these separate components, certainly during development of the model.

                   

                  To make a single step formula all that is necessary to combine the 2 parts. This you can do by substituting the formula from which the field 'WaD_Suffix' is created for the name of the field in any of the filter formulae above.

                   

                  There are many similar possibilities to be discovered.

                   

                  Welcome to the ever more liberating world of Monarch!

                   

                  Have fun.

                   

                  Grant

                   

                  [size="1"][ June 19, 2006, 04:31 PM: Message edited by: Grant Perkins ][/size]

                  • FILTERING
                    Dianne Hardin

                    Your explanations make perfect sense (scary).  I've had a lot of luck in the past with my experimenting and trial and error.  I sometimes wonder what I did without Monarch.  We have canned reports that extract information from our data base, but the formatting renders it useless.  Unless I can put it into Excel, it's of no use to me.  Monarch has changed everything.  Things that took days for employees now take minutes since I've been able to clean up their raw data.

                     

                    I again thank you for this new level of knowledge.

                     

                    Dianne