26 Replies Latest reply: May 15, 2014 10:01 AM by crazzi _ RSS

    Trapping Issue with PDF File

    crazzi _

      I just returned from a training session using Monarch. At that point you think you can do anything but now trying to do it on my own is definitely a challenge. I am using a PDF file to pull data and I can get what I need for the detail template but when trying to pull other data to append I'm not able to capture what I need. This is what I captured which is all one line in report

       

      00028604 04/06/2007      2000MG MERREM/INTERMATE 200ML/   0.00                    0.00                0.00           0.00              0.00           0.00         523.83       523.83

       

      I need to capture a few more lines and what I need to do is somehow incorporate the information in with the above info. This information is Patient Total, Payer Name, Payer Total, and Comments(multiple lines).

      Would this require getting more of the info in the detail then filtering out in the table? I also tried doing this using a .txt document but was unable to pull what I needed.:confused:

       

      Thanks

      Cindy

        • Trapping Issue with PDF File
          joey

          I know the feeling you're describing after training

           

          Can you please provide a sample of the report and what you'd like to include in your extract?

            • Trapping Issue with PDF File
              crazzi _

              Joey,

              I've tried to paste the sample but it doesn't line up the way the report is suppose to be. Is there a special way to get a sample to you since there isn't a way to attach a report??? I know it is going to be something simple.

               

              Thanks

              Cindy

                  • Trapping Issue with PDF File
                    crazzi _

                    Thanks and here goes:

                     

                    PAYER: 65 SPECIAL 2,158,605,407.00

                    Patient: Doe, Jane (215) 555-5555

                    Claim Service Date Current 31 - 60 61 - 90 91 - 120 121 - 150 151 - 365 >365 Open Balance

                    00028604 04/06/2007 2000MG MERREM/INTERMATE 200ML/ 0.00 0.00 0.00 0.00 0.00 0.00 523.83 523.83

                    User Name: DoeA                Date Time: 05/05/2008 09:53

                    Notes:Submitted via navinet.

                    User Name: DoeA                    Date Time: 03/21/2008 11:19

                    Notes: CLAIM IS PENDING.

                    User Name: DoeA                    Date Time: 02/08/2008 12:52

                    Notes: Emailed rep @ PHIT requesting Medicare EOB's to be faxed.

                    User Name: DoeB                    Date Time: 06/14/2007 12:42

                    Notes:

                    Claim has been billed through Claim Agent to Medicare only.[/B]

                    04/06/2007 Charges For Drugs &                                 641.23

                                     Supplies - Patient

                    04/19/2007 Billed, Primary Payer -                               641.23

                                    Medicare Reg

                                    A-Bfd-Drugs(Rn)

                    06/14/2007 Medicare Over Screen, Prior                      (117.40)

                                    Period - Medicare Reg

                                    A-Bfd-Drugs(Rn)

                    07/08/2007 Billed, Secondary Payer - 65                       523.83

                                    Special

                    Patient Total - Doe, Jane: 0.00 0.00 0.00 0.00 0.00 0.00 523.83 523.83

                    PAYER TOTAL - 65 SPECIAL: 0.00 0.00 0.00 0.00 0.00 0.00 523.83 523.83[/CODE]

                     

                    I was able to get some of the information that I needed for the templates and the last thing that I need to capture is User Name Line and the Notes line(BOLD). As you can see on this particular one the info for the Notes line is below it(can't fix that in the report) but these sometimes have more than one line. Tried a few different ways but unable to capture all the information.

                    Any help would be appreciated.

                    Thanks

                    Cindy

                      • Trapping Issue with PDF File
                        joey

                        First question, I notice on the first set of a User Name that the Date Time field is moved to the left.  Is this true in your source data as well, or was this an error when you dummied up the data before posting?  We can handle either case, but if the Date Time is in a reliable column always, it is easier.

                         

                        Second, have you tried using a detail line of the User Name / Date Time with capturing the other details as append templates?  For example, have one row for each User Name instead of one row for each Claim number.  Perhaps a better question would be to ask what you want as the final output? 

                         

                        As far as capturing the Notes, I did a 2 line detail template on the User Name.  The trap was the string User Name.  I definited the fields User, DateTime, and Notes-Raw.  Notes Raw was the entire second line begining in column 1 for the entire line.  Under the advanced properties I set end field on to "None of the above".  I set a footer template on the Patient Total line, so that the Notes captured everything.  Then I added a calculated field taking Notes Raw, and extracting everything but the first 6 characters ("Notes:").    This seemed to do the trick.

                          • Trapping Issue with PDF File
                            crazzi _

                            What I need as an end result is the Patient Name, the line of information below claim,service date,etc and the user name with date/notes. I used the claim, service date, etc line for detail which wasn't a problem then trying to incorporate this other info is. I realized that in my report the Patient name line on some is one character to the right then on others which inables me to capture them all. For so little lines to capture I'm getting stuck no matter what I've tried and of course the report is huge with a month worth of data.

                            For the sample I needed to move some of the information to show a more accurate look of the report.

                            Thanks

                            Cindy

                              • Trapping Issue with PDF File
                                joey

                                Do you intend to do any calculations on this report?  

                                 

                                If you capture the detail level at the user level and include the money fields from the claim level, they will be repeated four times for the example you provided. 

                                 

                                If you capture the detail level at the claim level, you could include all of the user info and notes as one big text string, but it would not be one line per user.  It would only have one instance of the money fields for each claim though.

                                 

                                It is possible to have the detail level at the user level and include the claim information for only the first user line of each claim and zeros in the money fields of the other user fields, but it is signifigantly more work.

                                  • Trapping Issue with PDF File
                                    crazzi _

                                    There will be some calculations for the report but will really be used as a working document that will be exported to excel. I have incorporated another sample because there could be many claims within a patient anywhere from one to more than ten. This will make the lines vary and not all claims will have Notes tied to them.  This alignment isn't the same as the report and if I tweak it there still is the potential for it to be off. Is this something that I should use Monarch with or just continue to use excel?? We need to blend several reports to get the information that is in this for our working document.

                                    PAYER: AETNA HMO.

                                    Patient: Doe, Jane

                                    Claim Service Date Current 31 - 60 61 - 90 91 - 120 121 - 150 151 - 365 >365 Open Balance

                                    00056977 03/19/2008 From Visit Tracking Module 0.00 133.00 0.00 0.00 0.00 0.00 0.00 133.00

                                    User Name: DoeA Date Time: 05/06/2008 11:24

                                    Notes: Claim denied on 4/7/08 for duplicate DOS. Sent to be reprocessed on 5/6/08.

                                    03/19/2008 Charges, Misc - Patient 133.00

                                    03/27/2008 Billed, Primary Payer - Aetna 133.00

                                    Hmo.

                                    04/16/2008 Denied - Other - Aetna Hmo. 0.00

                                    00058815 04/16/2008 COMBINATION OF 2 INVOICES 134.90 0.00 0.00 0.00 0.00 0.00 0.00 134.90

                                    04/16/2008 Charges For Drugs & 134.90

                                    Supplies - Patient

                                    04/16/2008 Charges For Supplies - Patient 0.00

                                    04/20/2008 Billed, Primary Payer - Aetna 134.90

                                    Hmo.

                                    Patient Total - Doe, Jane: 134.90 133.00 0.00 0.00 0.00 0.00 0.00 267.90[/CODE]

                                     

                                    Thanks for your help Cindy

                                      • Trapping Issue with PDF File
                                        joey

                                        Quick question on the report.

                                         

                                        There doesn't appear to be a User Name or Notes between the claim line 00058815 and the line 4/16/2008 Charges for Drugs.

                                         

                                        Is the lines 04/16/2008 charges for Drugs down until the Patient Total all Notes?

                                          • Trapping Issue with PDF File
                                            crazzi _

                                            Yes, but I only need to incorporate the ones that have the label of User Name and Notes. I just want what is new to the claim not the older notes. Again, not all claims have new notes and each patient could have more than one claim. Since there is so many inconsistancies with the lines of data that I need maybe Monarch might not work with this report to pull the data out.

                                            Thanks

                                            Cindy

                                              • Trapping Issue with PDF File
                                                joey

                                                I'm almost positive that Monarch can handle your situation.  You may need to involve Access.

                                                 

                                                Based on this, I believe your detail template should be on the claim number, assuming it is OK to display the user notes as one big string and contain one record per claim.  If not, read on down for other cases.

                                                The detail template should be the entire length of the line.  Name the field "DETAIL", and trap somewhere (I chose the slashes in the date).  Under the advanced tab, select "None of the Above" on "End Field On".

                                                 

                                                At this point, you'll need to set up a footer template for the Patient Total Trailer Record.  If you don't need to capture any information from here, you should still set up a blank template so that the DETAIL field picks up everything from a claim number up to before the Paitent Total line.

                                                 

                                                At this point, the claim information and the comments should be highlighted gray.  We're going to need to "slice and dice" the DETAIL field into the pieces you want. The key functions: LSplit(), RSPlit(), and TextLine().

                                                 

                                                Lsplit takes a string and splits into parts based on a separator starting from the left. It returns one of the parts that you specify.  If you notice on the Claim line there is a space only between the fields, so it makes a good separator for the first two fields.  RSplit is the same, but it searches from the right.

                                                 

                                                TextLine() returns the text on a given line.  Line(Detail,1) returns the claims portion.

                                                 

                                                I'm not sure what ("2000MG MERREM/INTERMATE 200ML/ ") is, or if it is constant.  I'll assume it isn't. 

                                                 

                                                First, use LSplit to get the first 2 fields. 

                                                 

                                                 

                                                LSplit(DETAIL,15," ",1) returns the Claim Number

                                                Lsplit (DETAIL,15," ", 2) returns the Date of Service

                                                 

                                                Next use Line and RSPlit to get the money fields.  Use a TRIM() to get rid of trailing spaces at the end of the line, and val to convert from a string to a number.

                                                 

                                                val(rsplit(RTrim(TextLine(DETAIL,1)),15," ",1)) = Total Due

                                                 

                                                Continue for the other money fields.

                                                 

                                                To extract the entire string of user names and comments:

                                                Extract(DETAIL, TextLine(DETAIL,1)) = NOTESBLOCK

                                                 

                                                That is to say, return everything in the detail line starting after the first line.

                                                 

                                                At this point we want to determine if the block of notes is new or old (determined if there is a user: or not). 

                                                 

                                                a new calculated field Notes should be:

                                                 

                                                if(instr("User Name:",NOTESBLOCK),NOTESBLOCK,"")

                                                 

                                                You can capture details from the Payer level, Patient level, or Payer total as append/footer templates if needed.

                                                 

                                                 

                                                This approach handles the case of the notes being multiple lines or not present at all.  It provides one line per claim, with the notes blank if they are all old comments.

                                                 

                                                If you want one record per User note, it is possible. 

                                                 

                                                If you don't want claims without User notes, then set the detail level at the user: line and make a 2 line template with the second line as notes and set the End Field on to No end.  You can capture the claims level fields with an append.  This is much more simple.  Note the detail level should probably be a floating trap to handle the moving Date Time: field.

                                                 

                                                However, if you want one line per user comment with claims information (even if there are no user comments) it becomes too big for Monarch (or possibly even Excel).  My approach would be to have two Monarch templates, one to capture the claims lines and another to capture the User notes lines (including the claims number from an append) in another model.  Each model should write out an Access table (CLAIMS and USER_NOTES).

                                                 

                                                You'll need to write a query

                                                 

                                                Select *

                                                FROM CLAIMS LEFT JOIN USER_NOTES

                                                ON CLAIMS.CLAIM_NUMBER = USER_NOTES.CLAIM_NUMBER

                                                 

                                                Finally, take this query as table input to a third Monarch model and manipulate the data (or export to Excel, or whatever you want).

                                                 

                                                Hopefully one of these three options will meet your needs.

                                                  • Trapping Issue with PDF File
                                                    crazzi _

                                                    Thank you for this information...I will see if any of this will work but I know that I won't get to it today.

                                                    Have a great weekend:D

                                                    Cindy

                                                      • Trapping Issue with PDF File
                                                        crazzi _

                                                        I'm trying to split a field but due to the data being off I can't seem to get all the dates in one column and the description into another. I could only trap this as one data field in order to capture all the dates per claim. I used the " " as a separator for the LSplit formula because I wasn't sure what else to use.

                                                         

                                                         

                                                        (From)      07/25/2007 COMBINATION OF 3 INVOICES

                                                                    04/02/2007     From Visit Tracking Module

                                                         

                                                         

                                                        (To)

                                                                        07/25/2007 COMBINATION OF 3 INVOICES

                                                        04/02/2007         From Visit Tracking Module

                                                         

                                                        /CODE

                                                        What am I missing??

                                                        Thanks Cindy

                                                          • Trapping Issue with PDF File
                                                            joey

                                                            Based on the samples you provided, I think the easiest thing would be to strip leading spaces and then pull the first 10 characters for the date, and the remaining string for the description.

                                                             

                                                            Date = left(ltrim(LINE),10)

                                                             

                                                            Desc = Substr(ltrim(LINE),11,len(ltrim(Line))-10)

                                                              • Trapping Issue with PDF File
                                                                Grant Perkins

                                                                A comprehensive looking solution from Joey.

                                                                 

                                                                It looks to me like the right trim (RTRIM() ) should[/I] be enough to allow you split the fields into 2 parts based on a space appearing after the date.

                                                                 

                                                                Part one woould give you the date column. Part 2 the rest - maybe with an RTRIM to deal with any extra spaces if the format is a little inconsistent.

                                                                 

                                                                A lot of variability would suggest the use of the more 'relative positional' processing that Joey proposes to great effect.

                                                                 

                                                                 

                                                                HTH.

                                                                 

                                                                 

                                                                 

                                                                Grant

                                                                  • Trapping Issue with PDF File
                                                                    crazzi _

                                                                    I was able to split out the fields...thank you

                                                                    I am now trying to age the invoices but since I needed use a character base for type in the columns how can I do this? I 've looked through other threads but was unable to see one that would help. I keep getting the error to change back the type to character or fix the formula (left(ltrim(),10) which I used to get my date of service info split out from my description.

                                                                    I want to use today's date and the date of service for the invoice to calculate the aging. Any suggestions???

                                                                    Thanks

                                                                    Cindy

                                                                      • Trapping Issue with PDF File
                                                                        joey

                                                                        To convert the text string "07/25/2007" to a date, you need the CToD function. 

                                                                         

                                                                        CToD(Input String,"m/d/y","d") should do the trick to return the date.

                                                                         

                                                                        I don't work with dates very often, so I pulled up the screen for a new calculated field and looked under the function category of Conversion.  I also pulled up the Help Manual under Chapter 2 - table Window - Functions - Function Reference for the arguements.  Any time you spend looking at these will pay off exponentially.

                                                                         

                                                                        As far as the aging report, I've not done this before, but give it a try.  Again, I found this by looking under the Date functions and the help window.

                                                                         

                                                                        Age(Report Date, Date(), 4) will return the number of days between the date on the report and today's date.

                                                                         

                                                                        Let me know if this works.

                                                                          • Trapping Issue with PDF File
                                                                            crazzi _

                                                                            Thank you....I was able to do the change the field to a date type so that I could use the age calculation function which was telling me I needed to fix the formula but I was struggling to find how. I guess it is all in the wording for a search in help to try and get the information needed.

                                                                            Thanks again:)

                                                                              • Trapping Issue with PDF File
                                                                                joey

                                                                                I had a college professor who told me that any time I spent learning a programming tool would pay off exponentially. I believe that is one of the most valuable pieces of advice I recieved.

                                                                                 

                                                                                I have to juggle several different programming languages and their built in functions every day, so I don't have every Monarch function at the top of my head. However, there are two methods I described that work well for me to find the function I need (and it's syntax, which varies for language to language).

                                                                                 

                                                                                First, to find the name of the function you want (for example to grab a few characters in the middle of a string), I filter the functions down to the string functions (upper right hand corner of the expression editor). I then scroll through the options.

                                                                                 

                                                                                If you've done any heavy model building, Excel spreadsheets, or programming, you find that you can have an idea of what each function does by the name. If not, look at the description in the yellow box at the botom of the editor.

                                                                                 

                                                                                Ususally the description and the function name is all that I need. In your case, I don't use CtoD (Character to Date) very offten, and I've never used the Age Function. For those, I needed the Help guide. I don't search for any strings, but navegate to the section of Working with Tables, and Functions to the Function Reference. I click the hyperlink of the function I want to know more about.

                                                                                 

                                                                                I think there is also a PDF reference available on Datawatch's website of all their functions. I've found the help document to be enough.

                                                                                 

                                                                                Spend some time playing arround with the functions in Monarch. Get to know what each one does. Version 9 has a VERY cool feature to let you test functions out with random values inside the editor, which help you test them out. I guarantee any time you do spend becoming more familiar with the functions will pay off tremendously. You'll see new ways to work with those troublesome reports, or even text documents that aren't reports.

                                                                                  • Trapping Issue with PDF File
                                                                                    crazzi _

                                                                                    Okay...now I have been asked to capture just first note within the invoices (along with Invoice #, Patient Name, Invoice Date, Description, Aging Dollars and Payer) and I've tried every which way I could think of but I am still not getting only the two lines I need for the UserName/Notes.[CODE]      AREA : PENNHOMEIT                                                                               

                                                                                    PAYER: 65 SPECIAL                                                                               

                                                                                    Patient: Doe, Jane                                                                    (215) 860-5407                                                                               

                                                                                    Claim Service Date                                                     Current       31 - 60      61 - 90       91 - 120      121 - 150       151 - 365          >365  Open Balance        

                                                                                          00028604 04/06/2007     2000MG MERREM/INTERMATE 200ML/                    0.00         0.00          0.00           0.00           0.00            0.00          523.83      523.83                                                                               

                                                                                    User Name:        WilsonCr                       Date Time:     05/19/2008 08:30                                                                               

                                                                                    Notes:            clm pd $47.66 on 05/16/08, chk# 018250532                                                                               

                                                                                    User Name:        JenkinCl                       Date Time:     05/05/2008 09:53                                                                               

                                                                                    Notes:            Submitted via navinet.                                                                               

                                                                                    User Name:        JenkinCl                       Date Time:     03/21/2008 11:19                                                                               

                                                                                    Notes:            CLAIM IS PENDING.                                                                               

                                                                                    User Name:        JenkinCl                       Date Time:     02/08/2008 12:52                                                                               

                                                                                    Notes:            Emailed rep @ PHIT requesting Medicare EOB's to be faxed.                                                                               

                                                                                    User Name:        JoyceB                         Date Time:     06/14/2007 12:42                                                                               

                                                                                    Notes:                                                                               

                                                                                    Claim has been billed through Claim Agent to Medicare only.                                                                               

                                                                                    04/06/2007   Charges For Drugs &                         641.23                                                                               

                                                                                    Supplies - Patient                                                                               

                                                                                    04/19/2007   Billed, Primary Payer -                     641.23                                                                               

                                                                                    Medicare Reg                                                                               

                                                                                    A-Bfd-Drugs(Rn)                                                                               

                                                                                    06/14/2007   Medicare Over Screen, Prior                (117.40)                                                                               

                                                                                    Period - Medicare Reg                                                                               

                                                                                    A-Bfd-Drugs(Rn)                                                                               

                                                                                    07/08/2007   Billed, Secondary Payer - 65                523.83                                                                               

                                                                                    Special                                                                               

                                                                                    Patient Total - Levy, Karen:                                        0.00         0.00          0.00           0.00           0.00            0.00         $523.83     $523.83                                                                               

                                                                                    /CODE

                                                                                     

                                                                                    There are some invoices that don't have any user notes and some with one, two or more but only want first one. I need to have all invoices in my report whether with a note or not. With the PDF files I find that I need to do alot of manipulating of the columns in the table due to the placement of information which seems to change throughout the report.

                                                                                    Thanks

                                                                                    Cindy

                                                                                      • Trapping Issue with PDF File
                                                                                        Data Kruncher

                                                                                        Hi Cindy,

                                                                                         

                                                                                        I'm not sure how well you'll be able to integrate this with any other requirements within the same model, but try this out.

                                                                                         

                                                                                        Make the line with the claim number your detail line. I trapped on NN/ for the date.

                                                                                         

                                                                                        Now add a four line append template, beginning with the same line, so that the first note is on line four. Trap exactly the same NN/ on line one, and paint the notes field on line four.

                                                                                         

                                                                                        This will add only the first note to your detail, ignoring the rest, and you'll still get every invoice in your detail.

                                                                                         

                                                                                        HTH,

                                                                                        Kruncher

                                                                                          • Trapping Issue with PDF File
                                                                                            crazzi _

                                                                                            Thank you ...that does help but I have the issue of invoices that don't have a user note and how not to capture the data in the first few lines of those but keep it in table. I can export into excel and strip it out but is there some way to do it in the template? I've tried various trappings and  but to no avail and I've tried the different settings in the advance tab but still couldn't find anything. Also, I tried to see if I could do a filter or calculation but I don't want to remove those invoices from the table.[CODE]   00068633 07/04/2008     8MG ZOFRAN/BD SYRINGE 10CC                    1,767.72         0.00          0.00           0.00           0.00            0.00            0.00    1,767.72                                                                               

                                                                                            User Name:      JoyceB                         Date Time:     07/17/2008 10:27                                                                               

                                                                                            Notes:          Submitted claim to Aetna via CA 7/16/08                                                                               

                                                                                            07/04/2008   Charges For Drugs &                       1,767.72                                                                               

                                                                                            Supplies - Patient                                                                               

                                                                                            07/09/2008   Billed, Primary Payer - Aetna             1,767.72                                                                               

                                                                                            Hmo.                                                                               

                                                                                            00068637 07/04/2008     TPN 0 Grams Protein/Day                         721.01         0.00          0.00           0.00           0.00            0.00            0.00      721.01                        

                                                                                                  07/04/2008   Charges For Tpn & Supplies -                721.01                                                                               

                                                                                            Patient                                                                               

                                                                                            07/09/2008   Billed, Primary Payer - Aetna               721.01                                                                               

                                                                                            Hmo.                                                                               

                                                                                            /CODE

                                                                                            Thanks Again for your help

                                                                                            Cindy

                                                                                              • Trapping Issue with PDF File
                                                                                                Data Kruncher

                                                                                                Cindy,

                                                                                                 

                                                                                                If you go to the Advanced tab of your Notes field, and specify "notes:" as the preceding string in current line value, you should be OK. You'll get all of your invoice detail lines, and only append note values when they exist, instead of any miscellaneous stuff that happens to be in the right position in line 4.

                                                                                                  • Trapping Issue with PDF File
                                                                                                    crazzi _

                                                                                                    I had tried that and it didn't seem to work for me so I guess it must be the fields that I am highlighting for the template and then trying to use the options in the advance tab. My detail template is the claim line with all the data in separate fields. The first append template is for the user notes inwhich I highlighted the four lines and trapped them. I used the same info to trap as I did with the detail template then I also highlighted the info on the third line and fourth line that I needed. I don't actually highlight the words User Name, Notes or Date Time. I didn't highlight anything within the second line since there isn't any data. Could that be my issue???

                                                                                                    Thanks

                                                                                                    Cindy

                                                                                                      • Trapping Issue with PDF File
                                                                                                        Grant Perkins

                                                                                                        Cindy,

                                                                                                         

                                                                                                        If you only need the "Notes:" line field(s) for your append make the data sample for the append a single line. The actual data in the line does not matter at all in this case other than to help you get the horizontal position of the field(s) you need a little more easily. Make the trap for the template exactly the same as youe detail template trap.

                                                                                                         

                                                                                                        If you also wanted to grab the user name, etc., then you would probably need 2 lines for the sample assuming the preceding string fields would overlap if you tried to place them on a single line.

                                                                                                         

                                                                                                        Don't worry about only one line in the sample. Monarch will read from your trap line looking for a string in the horizontal position of your field preceded by the character string "notes:" until it finds one (it will only select the first occurrence, not several lines) OR the process gets to the next DETAIL template at which point, with the append set up using the same trap as the detail, the append will be reset in line with the new detail record.

                                                                                                         

                                                                                                        Does this move you forward?

                                                                                                         

                                                                                                        Grant