7 Replies Latest reply: May 15, 2014 9:57 AM by Grant Perkins RSS

    multi-line help please

    rok _

      Hi,

       

      New user to Monarch here (version 8.02 Pro).  I'm working with a multi-line report, and I'm having trapping my info.  Here is a sample of the report (fixed-length):

       

      [font="courier"]1000                           COLLECTION,SALLY              45968 E SIDE LN                                             BEVERLY HILLS       CA 90210    45369499911526               6708026082000934F

      2COLLECTION,SALLY              45968 E SIDE LN                                             BEVERLY HILLS,CA              90210               6082000934SELF                                           

      3FLEET OF FEET       18 LYSINCK RD       52202               NASHOTAH          WI53058    6083985938FLEET OF FEET       18 LYSINCK RD       52202               NASHOTAH          WI53058    6083985938

      4AINSURANCE COMPANY1           UNIFIED MASTER PLAN                                                             ,                                             COLLECTION,SALLY                         

      4BINSURANCE COMPANY2           UNIFIED MASTER PLAN                                                             ,                                             COLLECTION,SALLY                         

      4CINSURANCE COMPANY3           UNIFIED MASTER PLAN                                                             ,                                             COLLECTION,SALLY                         

      5000009SMITH, PEGGY        THE CLINIC         000001152607214070802COLLECTION,SALLY    SMITH, PEGGY        9  07080299215 OFFICE/OUTPT VISIT,EST,L    000100.00828  V70.0                             

      5000009SMITH, PEGGY        THE CLINIC         000001152607214070802COLLECTION,SALLY    SMITH, PEGGY        9  07073199213 OFF/OP, EST, EXP PROB FO    000300.00828  V70.1                             

      6000000000000000000000000000000000000.00000000.00000000.00000400.00000400.00070802                                                                               

      1000                           COLLECTION,BOBBY              45968 E SIDE LN                                             BEVERLY HILLS       CA 90210    23448486711527               5708026082000934M

      2COLLECTION,SALLY              45968 E SIDE LN                                             BEVERLY HILLS,CA              90210               6082000934SPOUSE                                         

      3                                                                                FLEET OF FEET       18 LYSINCK RD       52202               NASHOTAH          WI53058    6083985938

      5000009DEFAULT, PHYSICIAN  WEST CLINIC        000001152607214070802COLLECTION,BOBBY    DEFAULT, PHYSICIAN  9  07080299213 OFF/OP, EST, EXP PROB FO    000230.00828  V70.3                         D1234

      6000000000000000000000000000000000000.00000000.00000034.50000195.50000230.00070802                                                                                /font[/quote]I need to trap lines 1-6 (as 1 record), but as you can see, the number of lines vary.  All I know for sure, is line 1 will always be there, and line 6 will always be there.

       

      Thanks for your time.

        • multi-line help please
          Grant Perkins

          Hi rok and welcome to the forum.

           

          I have been looking at your posted sample for a while now and it does not immediately present a clear view of the way forward.

           

          There are a number of techniques that one could apply but, absent any 'tags' for the data lines (other than line numbers which I suspect you have added to help illustrate the example) you are left with a seemingly large number of possible variations that make the entire report rather unwieldy.

           

          Trapping the multiple lines is not too much of a problem. But doing anything with them afterwards looks quite challenging.

           

          What do you need as output?

           

          Is your proposed output logical in relation to the data available?

           

          Sorry for the questions but it seems to me that the solution to this one is very dependent on the data in the details.

           

          Grant

          • multi-line help please
            rok _

            Grant,

             

            Thanks for your reply.  Fortunately, those tags are included in the file. 

             

            Each line references a different category of infomation I need.  Example, line 1 contains the names and addresses, line 3 contains employment information, line 5 contains dates, and amounts.

             

            So my desired output would be simalar to something like:

             

            [font="courier"]NAME              ADDRESS         CITY          ST ZIP   POS         DATE   CHARGE

            COLLECTIONS,SALLY 45968 E SIDE LN BEVERLY HILLS CA 90210 EAST CLINIC 070802 00400.00

            COLLECTIONS,BOBBY 45968 E SIDE LN BEVERLY HILLS CA 90210 WEST CLINIC 070802 00195.50[/font][/quote]The method I was (am) trying is working with 1 line at a time, simply using the line tag as my trap, then adding ammends for the other lines, 1-6.  I'm getting confused which line should be "detail" and which should be "ammends".

             

            Hope I'm making sense, very new to this.  Thanks again for your help.

             

            rok

            • multi-line help please
              Grant Perkins

              Originally posted by rok:

                I'm getting confused which line should be "detail" and which should be "ammends".

               

              Hope I'm making sense, very new to this.  Thanks again for your help.

               

              rok /b[/quote]OK, if the lines are identified by the numbers it sort of makes life a little easier, maybe, though I guess you also have to consider then 4A, 4B, 4C etc.

               

              I entirely agree with you about the confusion over which lines need to be detail.

               

              On the other hand your output example suggests you only need data from lines 1 and 6. Is that the case or am I just indulging in wishful thinking?

               

               

              Grant

              • multi-line help please
                rok _

                I need info from all 6 lines (if they're available).  Each record will have the same field names, if the info isn't available (not included in the report), the field will still be populated with a null value.

                 

                So in my example above, record 1 (by record I mean lines 1-6) will have insurance1 populate, whereas in record 2, insurance1 will be null.

                 

                rok

                • multi-line help please
                  Grant Perkins

                  Originally posted by rok:

                  So in my example above, record 1 (by record I mean lines 1-6) will have insurance1 populate, whereas in record 2, insurance1 will be null.

                   

                  rok /b[/quote]OK, the insurance company lines seem to have 4a, 4b , 4c and presumably further identifiers if required. Can you also differentiate the '5' lines using the same method?

                   

                  Do you know the maximum number of lines in each sections (and therefore the maximum number of fields you might have in a single record related to a single patient? Can you use the "4" lines as single field 'blocks' or would each line need to be split into separate fields?

                   

                  How do the "5" lines relate to the "4" lines? ( I assume that the "5" lines data do not all related to each of the "4" lines records.)

                   

                  All of these question will help in defining how complex or simple the soloution may become and also establish just how many fields will be required.

                   

                  Hopefully.

                   

                  Grant

                  • multi-line help please
                    rok _

                    Can you also differentiate the '5' lines using the same method?[/quote]You can differentiate, but only by using the date (line 5, position "62, 6" or "111, 6").

                     

                      Do you know the maximum number of lines in each sections and therefore the maximum number of fields you might have in a single record related to a single patient?[/quote]Unfortunately not, its hard to say because the number of line 5 vary (depending on how many visits the patient made to that particular office).  Line 5 contains dates, and charges primarily. In the output, I'll use the newest date, and sum the charges.  Now I can output 1 date, and 1 total charge, in a sense saying "as of this date, you are responsible for this total" 

                     

                    Also lines 4a/b/c will either be there, or not be there, simply indicating no insurance, primary insurance (a), secondary insurance (b), and/or a third insurance (c). The "actual original" test file given to me only included 4A.  I added 4B, and 4C because like you, I was curious how many lines total could be involved here.

                     

                       How do the "5" lines relate to the "4" lines? (I assume that the "5" lines data do not all related to each of the "4" lines records.)[/quote]You're correct, no relation.

                     

                    I tried to do a mini-analysis this morning.  My output report ideally will have 42 fields.

                     

                    Of the 42 fields:

                    9 are from line 1

                    7 are from line 2

                    7 are from line 3

                    4 are from line 5

                    3 are from line 6

                    12 are from lines 4a/b/c

                     

                    So I'm collecting a potpourri of data from all over this thing and hopefully the outcome will be 1 line, with 42 fields (although 12 could possibly be null).

                     

                    My original example is exactly how it was presented to me.  The tags are 1, 2, 3, 4A, 4B, 4C, 5, and 6.

                     

                    Line 1 is patient info, line 3 is employment info, and 4B is secondary insurances etc. Hope this helps, thanks again Grant.

                     

                    rok

                    • multi-line help please
                      Grant Perkins

                      Hmm.

                       

                      The individual components of this do not seem too challenging but the totality is.

                       

                      Lets look at the easy bits.

                       

                      You can trap and map line 1 (as detail let us assume for now) and line 6 (probably as a footer) without too much difficulty.

                       

                      It looks like (and the nature of the data suggests) you may also always have a line 2, in which case you could make that part of the detail template and have a 2 line template.

                       

                      If line 2 does NOT always appear try this. Create an APPEND template using exactly the same trap as for the line 1 detail template. Paint a field FOR the entire width of LINE 2 EXCEPT the first character column. In the advanced field properties tab set the field to START ON a preceding string of "2" (without the quotes). By doing this Monarch will start at the same point as the detail template and simply scan down the rows until it finds the preceding string OR another detail template. You will either get a populated or empty field in your table.

                       

                      Hopefully that will give the the entire line 2 only when there is one. You can slice and dice the resulting string using calculated fields to extract the sections of data you require. The SUBSTR() function is most likely to be useful here since the fields you need probably start and and at known positions on the line.

                       

                      Comments for line 3 are the same as line 2. Indeed it may be possible to make the detail template a 3 line template if line 3 always exists. (In which case you won't need the slice and dice techniques!)

                       

                      So that leaves the 4s and 5s

                       

                      In theory you could use the same approach and simply make the long field extraction a multi-line field by setting a suitable "End Field On" value in the field advanced properties tab BUT the repitition of the number would create difficulties, especially for the "5" lines which seem to have no subsequent identifier for line uniqueness. It is also not entirely clear how you might set a reliable and consistent End On value ...

                       

                      (If you can extract the 'block' of lines, probably into a MEMO field, you can separate them into record sets using the TEXTLINE function with either the LINE NUMBER or the SEARCH attributes - if you end up going down that route I can cover those options in more detail then.)

                       

                      So at this point I am beginning to think that you really need to consider the solution to be a multi-step process.

                       

                      Here is the outline in my mind.

                       

                      Trap and extract the "4" lines as detailed separate lines and their fields (or maybe a single Multi-line block BUT your trap would have to be other than the number 4 in the first character column for that to work. If the 4 lines always have an identifying LETTER even if there is only one line you could use 4A I guess, in which case the multi line field using preceding string "4A" may work). Append a unique reference from the 1 line to act as a lookup key. Export the resulting table to you choice of an Access/Excel/delimited file/fixed width text file ready to be used as a lookup table.

                       

                      Do the same with the "5" lines.

                       

                      Now create a 'master' model that extracts lines 1,2,3 and 6 and performs an external lookup to the just created files for the 4 and 5 lines or which ever parts of them you require.

                       

                      You have the option to split the fields for 4 and 5 lines in that first extraction phase or leave then as entire line and split them up later. I suspect the early split might be easier but could be wrong.

                       

                      How does that sound so far?

                       

                      The entire process can be automated to a single batch file if required so running it should be less complex than setting it up!

                       

                      How much sense does all that make? Let me know and I will try to clarify the unclear.

                       

                      HTH.

                       

                       

                      Grant