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

    Filtering with Text String

    jbvinny _

      I have a table I need to filter. In the column titled "Description" I want to keep any row which contains "Lockbox".  However the problem I am having is that Lockbox may appear anywhere in the cell with different words before or after it. 

       

      Is there a Contains function in Monarch?  

       

       

      I hope this is clear. I apologize if this is overly simplistic I am a new Monarch user.  I appreciate any help.

        • Filtering with Text String
          Data Kruncher

          There's never a need to apologize when asking questions.

           

          The Monarch equivalent of Contains is Instr.

           

          Instr returns an integer value equal to the starting position of the search string within another string (the "target" string). Instr will return zero if the search string does not appear in the target string.

           

          With that, here's the expression for your filter:

          [SIZE=2]Instr("Lockbox",[Description])>0[/SIZE][/CODE]

           

          HTH,

          Kruncher

            • Filtering with Text String
              jbvinny _

              Perfect thanks!  I looked at Instr a few times..but the description did not seem to be what i was looking for.

                • Filtering with Text String
                  crazzi _

                  Is there a way to do this type of filter with a few different words? I'm looking to pull all the payments within a report and they are all spelled different(ie:payment,paid,pymt,etc). I then want to create a filter that will pull everything except the payments. I was able to pull the payments using the Right function but I didn't get the records where the word for payment was previous to the last word. When I changed the operator to <> in another filter to get the other records it just gave me everything in the file.

                  Thanks for your help....

                  crazzi

                    • Filtering with Text String
                      Data Kruncher

                      .Not. (

                      InStr("paid",Desc)>0 .Or.

                      InStr("pmt",Desc)>0 .Or.

                      InStr("payment",Desc)>0)

                      /CODE

                       

                      You'll need to specify every possible spelling and misspelling in order for this to work properly. This approach will work, but it's not very robust.

                       

                      Are you sure that there isn't a better way to check for a payment within your report?

                        • Filtering with Text String
                          crazzi _

                          The only other field that I would be able to use is the Transaction Code and there are also various codes used so I would need to know all that could potentially be in a file. This report consists of both payments, adjustments and transfers which I would like to have a report for each. You're right about the spelling variations of payment so I would imagine you would think that I am better off with the transaction codes.

                          Here's a sample of report but I have removed Name, Acct No, Date of Service and it isn;t lining up right since I removed that info.

                           

                               301499  BLUE CROSS PAID                               .00  R62

                                         517391  OPD PATINS TRANSFER                   100.00  B95

                                         151506  OP MANUAL ADJ - BLUE CROSS          32.00  B95

                                         517391  OPD PATINS TRANSFER                  -100.00  B95

                                         93195   BAL XFR DED/CO PAY         VT          -41.60  B91

                                         301499  BLUE CROSS PAID                         -374.40  B91

                                         93195   BAL XFR DED/CO PAY         VT            41.60  Q70

                                         301499  BLUE CROSS PAID                                .00  R62

                                         301499  BLUE CROSS PAID                                .00  R62

                                         19893   OP LOCKBOX INS PAYMENT             23609.04

                                         151605  OP MANUAL ADJ - MANAGED CARE  -23609.04

                                          24208  POS CASH I-PAY                            21721.12

                                                                                                     -21721.12  C87

                                         301499  BLUE CROSS PAID                          -1236.13  R62

                                                                                                          .00  R62

                                         156802  OP MANUAL ADJ-MANAGED MEDICAR         .01  R62

                                         301499  BLUE CROSS PAID                           -8981.50  B95

                                         301499  BLUE CROSS PAID                             4188.75  R62

                                         301499  BLUE CROSS PAID                             -213.13  R62

                                          93195  BAL XFR DED/CO PAY         VT              -72.60  B62

                                         301499  BLUE CROSS PAID                              -737.80  B62

                                          93195  BAL XFR DED/CO PAY         VT                 72.60  265

                                         156802  OP MANUAL ADJ-MANAGED MEDICAR     -1501.74  265

                                         301499  BLUE CROSS PAID                               2653.00  R62

                                                                                                          .00  R62

                                         301499  BLUE CROSS PAID                              -1186.13  R62

                                         156802  OP MANUAL ADJ-MANAGED MEDICAR             .01  R62

                                         301499  BLUE CROSS PAID                                      .00  R62

                                         151605  OP MANUAL ADJ - MANAGED CARE           -606.14  R52

                                         301499  BLUE CROSS PAID                                 6785.04  B85

                                                                                                          -14163.33  B85

                                         517391  OPD PATINS TRANSFER                            262.46  B95

                                         500181  W/O NO PRECERTIFICATION ADJ                -54.28  R62

                                         19893   OP LOCKBOX INS PAYMENT                     -1051.60  R85

                                         601096  USHC PIP PAYMENT                                  -60.06  R86 /CODE

                           

                          Thanks

                          Cindy

                            • Filtering with Text String
                              Data Kruncher

                              If you know that the transaction codes are always applied to reflect a particulary activity properly, then that's definitely what you'd want to use. The filtering approach would be similar, maybe along the lines of:

                               

                              Code .NotIn. ("B95","R62")[/CODE]

                               

                              etc., etc. as required for more transaction codes.