9 Replies Latest reply: May 15, 2014 9:58 AM by joey RSS

    Filter Help?

    HCA _

      I'm a rookie when it comes to setting up filters thru monarch.  i need help in setting up one.  Below is example data to help explain what it is i am trying to accomplish

       

      [font="courier"]INSURANCE     VERIFIED_DATE     AUTH_NUM

      MCD.COMSTR          

      MCD.SUPSTR     09/17/07     PENDING

      MCD.SUPSTR     09/28/07     PENDING

      MCD.SUPSTR     09/28/07     PENDING

      MCD.OOS          

      MCD.SUPSTR     09/28/07     PENDING

      MCD.COMSTR     09/28/07     PENDING

      MCD.COMSTR     09/28/07     PENDING

      MCD.PCCM     09/27/07     PENDING

      MCRB.IP             09/25/07     

      MCRB.IP      09/27/07     1zx234

      MEDICARE     09/20/07     

      MEDICARE     09/19/07     

      MEDICARE          

      MEDICARE     09/24/07     

      MCD.PEND          

      UN.INSUR          

                /font[/quote]What i need is to filter out any INSURANCE with either no VERIFIED_DATE OR no AUTH_NUM OR AUTH_NUM says "Pending" but i need to exclude"INSURANCE that are "MCD.PEND" and "UN.INSUR".  Where i'm getting stumped is in regards to MEDICARE insurance i only need to see the ones with NO VERIFIED_DATE and dont care if there is no AUTH_NUM.  I hope this isnt too confusing in what i am trying to do. I was able to accomplish some of the filtering using the 'Isblank' function. 

       

      Any and all help is greatly appreciated.

        • Filter Help?
          Data Kruncher

          Hi HCA,

           

          I think what you're after is along the lines of:

          [font="courier"]INSURANCE  .NotIn.("MCD.PEND", "UN.INSUR")  .And.

           

          (.not. isnull()     .And.   

          .not. (isblank(AUTH_NUM)  .Or. AUTH_NUM="PENDING"))  .Or.

          (INSURANCE="MEDICARE"  .And. isnull())  /font[/quote]How's that?

           

          Kruncher

          • Filter Help?
            HCA _

            Thanks for the response 

             

            I did try your filter and I am still getting back rows of other insurances that do have a VERIFIED_DATE OR AUTH_NUM which i'm hoping to filter out.

            • Filter Help?
              HCA _

              I think i was able to get it be doing some modifications to your filter DATA KRUNCHER:  This is what i did and it looks to be what i was needing (i had to add additional insurances in the exclusion list cause i found some more)

               

              INSURANCE  .NotIn.("MCD.PEND", "UN.INSUR", "SP.PROMPT", "CHAR.PEND", "MEDICARE") .And. (isnull() .Or.  (isblank(AUTH_NUM)  .Or. AUTH_NUM="PENDING")) .Or. (INSURANCE="MEDICARE"  .And. isnull())

              • Filter Help?
                Grant Perkins

                HCA,

                 

                It looks like you have your solution but you might like to consider Compound Filters as an possible alternative.

                 

                Using a compound filter (see the Help file for some concepts and examples) allows you to start with  'simple' filter expressions and then combine them in more complex ways. (And then combine the compound filters created into even more compound filters ...)

                 

                The benefit being that each individual filter can be kept relatively simple and, should it need to change if, for example, another Insurance Type appeared and needed to be excluded you would simply add it to the "Insurance" filter and it would automatically also apply to any compound filters using the "Insurance" filter as part of their definition.

                 

                So in effect you create simple filters and Monarch does the job of working out the logic in the formula for you whebn you tell it how the spearte filters work together - just as you described in your first post.

                 

                See what you make of it. If you get stuck let us know and I will have a look at the specific issues.

                 

                Have fun.

                 

                 

                Grant

                 

                [size="1"][ October 09, 2007, 09:39 AM: Message edited by: Grant Perkins ][/size]

                • Filter Help?
                  HCA _

                  Thanks Grant for the tip.  I do have one additional question regarding filtering.  For my AUTH_NUM field i need to use wildcard option.  Some AUTH_NUM will say "AUTH PENDING" or "PENDING DELIVERY" and some just say pending.  I thought of using the wildcard string of :

                   

                  AUTH_NUM="PEND*"

                   

                  which gives me the ones with the word PEND and any following data.  How can i set it up to also capture "AUTH PENDING" ?  I tried using AUTH_NUM="PEND" but that gave me back everything.

                  • Filter Help?
                    joey

                    The function instr(AUTH_NUM,"PEND") > 0 will do the trick of seeing if the AUTH_NUM field contains the substring PEND.

                    • Filter Help?
                      Grant Perkins

                      HCA,

                       

                      Joey's suggestion is exactly what I would do for that example.

                       

                      There may be similar requirements that would make use of some of the other functions available or even combinations of them and I would greatly recommend taking every opportunity to experiment with the functions to help make them an natural integral part of your Monarch toolkit. Results  can be obtained so much more easily when you know there is a solution in there somewhere even if precisely how to deliver it does not come instantly to mind! A few seconds browsing the functions available is usually enough get started in the right direction once you have a feel for the potential.

                       

                      Grant.

                      • Filter Help?
                        HCA _

                        I apologize but i am not very experience with monarch, so this is how my filter looks now:

                         

                        INSURANCE  .NotIn.("MCD.PEND", "UN.INSUR", "SP.PROMPT", "CHAR.PEND", "MEDICARE") .And. (isblank() .Or.  (isblank(AUTH_NUM)  .Or. AUTH_NUM="PEND*")) .Or. (INSURANCE="MEDICARE"  .And. isblank())

                         

                        Where in the filter would I put the line for :

                        instr(AUTH_NUM,"PEND") > 0

                        • Filter Help?
                          joey

                          /size[quote]code:[/size]INSURANCE .NotIn.("MCD.PEND", "UN.INSUR", "SP.PROMPT", "CHAR.PEND", "MEDICARE") .And.

                          (isblank() .Or. (isblank(AUTH_NUM) .Or. instr(AUTH_NUM,"PEND") > 0)) .Or. (INSURANCE="MEDICARE" .And. isblank())

                           

                            /QUOTEI think that is the code you want. I just replaced AUTH_NUM="PEND*"