7 Replies Latest reply: May 15, 2014 10:02 AM by Grant Perkins RSS

    Multiple Line Data Trap

    crazzi _

      I have a report that has info on multiple lines but some have only one. I'm looking to see the data in a horizontal format for each patient. I've had no problem capturing the main data but can't quite seem to capture the rest due to no common element.

       

      [CODE  205440639 Doe, John M        ASL D   1 MM 112907     750.00  12200710554.45- 0 M  4 K10       .00      750.00  061108 7B                                                                               

      M28        .00                       

      205163181 Doe, Mary                   ODL D   2 FM 100107     150.00  111207* 2300.25- 0    5 K10*    150.00         .00  053008 7S

      204115653 Doe, Joe                     OD8 D   1 MM 021307     298.10  090507*     .00  0    1 E02        .00      298.10  031608 9 

      206290132 Doe, Michael                OF3 D   2 FS 041408      45.90  051208*  183.60- 0    2 C35*       .00       45.90  061308 3 

      205204381 Doe, Barbara                OES D   2 MS 100407    3255.00  NONE      .00  0    1 S01        .00     3255.00  012808 7 

      206406464 DOE ,SAMUEL               OC2 D   1 MM 050608     249.28  061608*     .00  0    1 M99        .00      249.28  062108 2 

      206426835 Doe ,SAMUEL               OR2 D   6 MM 043008     335.00  NONE      .00  0    0 A28        .00      335.00  NONE 3                                                                               

      D15        .00                       

      206540619 Doe ,TANYA                 OE3 D   2 FM 052908      50.00  061208*  620.00- 0    1 J01*       .00       50.00  062108 2 

      205735228 Doe ,TYRONE               OES D   2 MS 013008    7763.00  NONE      .00  0    2 S01        .00     7763.00  060508 6 

      205248768 Doe ,NANCY                 OR2 D   6 FS 100807      33.34  111307*     .00  0    2 M99        .00       33.34  061408 7                                                                               

      B65        .00                                                                               

      B00        .00                                                                               

      B01        .00                       

      205717101 Doe ,MARY                  ASL D   2 FX 012808    1024.00  022008* 5447.36- 0 M  3 K10*       .00     1024.00  061208 6                                                                               

      M28        .00                                                                               

      P20        .00                       

      ][/CODE]

       

      The Row with Alpha/Numeric (ex:M28) then there is a dollar amount after which is all that is in that row. Most of the patient's have one insurance code but others have two but can go to four. The traps I've tried capture the next patient's data and incorporate it into both. I've tried incorporating the info in one field for each (into Alpha/Numeric and $$) and splitting but ran into trouble with the money column. I've chatted with others who use Monarch and they haven't been able to do this....hopefully you can help.

      Thanks

      Cindy

        • Multiple Line Data Trap
          Grant Perkins

          Cindy,

           

          Good challenge.

           

          One approach would be to use Monarch to reformat the report onto a single line per record, export the resulting single row per main record report and then model the lines from that.

           

          Trap for you main line as usual but select the entire report width as a single field and make it a multiline field using the Advanced Property for 'End On' = None of the above.

           

          The extra lines will now be part of the single line, provided the field is set to be wide enough on display.

           

          Export the result to a fixed width report file and template the fields from that.

           

          The fields in the lines don't look consistently positioned as posted but that may be a C&P problem rather than an original format problem?

           

          If there are issues modelling the exported file just let us know.

           

          I can't think of a way to do this without first modifying the report in some way UNLESS you are prepared to do a lot of slicing and dicing of the long single field using many calculated fields. If the fields in the lines will all align by a start position for each one and with consistent length it might prove quite easy to do. But if they don't it's possible to slice and dice but maybe not with guaranteed consistency.

           

          HTH.

           

           

           

          Grant

            • Multiple Line Data Trap
              crazzi _

              Just to add a bit more interest to this...I only need a portion of the data from the report which belongs to us. Do you see an issue with this when using your suggestion of reformatting the report first?? I was using a filter to capture what I need and just making sure that it will still work. Here's the header of the report in case that might change something with your suggestion[CODE]SELECTED PA AGED TRIAL BALANCE BY DIST/PT NAME   Facility XXX

                                    IRS# XX-XXXXXXX               DATE 06/30/08 PAGE    1   

              INDEX = 000 DOE ,ELDEN                           00020653386                               SELECT-SEQNO =  100  AGED FROM LAST ACTV DATE   

              DIST CODE = 000                                  

                                                    FPF R C      A-DSCH    ACCOUNT    LAST--PAYMENT  # # OF       INSURANCE   PATIENT    BILL  AP    

              ACCOUNT NO. -


              PATIENT NAME------ ITC C R R SM O-ADMIT   BALANCE    DATE   AMOUNT PM STMT INS    BALANCE    BALANCE    DATE  II                                                                               

              /CODE

               

              I use the DIST CODE to determine which are my accounts.

              Another bit of info...the origianl file is a text document which I should have mentioned first and I don't see anthing that suggests a fixed width report file. I've tried the various export file type options and they bring me the same report look.

               

              Thanks

              Cindy

                • Multiple Line Data Trap
                  Grant Perkins

                  Hi Cindy,

                   

                  In this case I don't think you really need to replicate the entire report for the new ouptut. Maybe not even output to a new file either of course.

                   

                  Just try what I suggested with the detail records then append what ever else you need, like the Dist Code, in the normal way. That retains your filter option.

                   

                  You can then just export the single wide field as a new report or run with the calculated fields within the same model PROVIDING the lines don't keep shifting around. It looks like the original is really all in columns, so you should be OK for the main parts of it. The extra lines added ad the ends of the primary lines might not be quite so well controlled but there are ways around that as well.

                   

                  If you do export to a new file be sure to use a non-proportional font  - for instance FIXEDSYS - where all the characters are the same width. That should maintain the columns as they need to be and make the mapping in the model easier. When exporting you should see an option to elect for fixed width or a delimited file. Just select fixed width and make sure the file name has a suitable file extention identifier. .PRN for example.

                   

                  On the other hand if the real file does give well aligned columns (the origianal posted sample does not but that is often the case when posting into forums) I would just use the 'slice and cide' techniques to extract the fields since you would know where they are on the line - except maybe for the added extra lines. The SUBSTR() function is one of the very powerful functions available to you for that purpose.

                   

                  If for some reasson the fileds are not in accurate columns we need to get a bit trickier but we would still have some options available.

                   

                  See how you go with that information. If the real report proves to have some hidden challenges we may need to dig somewhat deeper.

                   

                  HTH.

                   

                   

                  Grant

                    • Multiple Line Data Trap
                      crazzi _

                      Okay..I was able to export the info and bring in the report. I am able to capture the information that I need but I would like to see how to get all the information on one line for each account. The only info in the additinal line is a three digit code and $$.

                      [130      Facility Name     2008-06-30     205389620       Doe ,ALICE     O     R     J     J          2007-11-09     100          0          0     J01     100     0          7

                      130     Facility Name     2008-06-30     204033674  Doexxxxxx ,TARIQ     O     E     7     J          2007-01-19     -501.6     2008-02-11     -1120.6     M     3     W01     0     -501.6     2007-09-27     9

                      130     Facility Name     2008-06-30                                                                      J01     0               

                      130     Facility Name     2008-06-30     203057708     Doe ,LOTTIE     A     S     6     F          2006-07-28     952     2006-08-21     -8928.82     M     0     A26     0     0     2006-08-02     9

                      130     Facility Name     2008-06-30                                                                      Q65     952               

                      130     Facility Name      2008-06-30     205853054     ABI-   Doe ,JULIETTE     O     R     B     J          2008-02-18     60     2008-03-10     -116.96          0     B05     60     0          5

                      130     FAcility Name     2008-06-30     203542493     XXXXXXXXX ,JULIA     O     R     M     J          2006-09-20     105.53     2007-04-24     0          0     M99     105.53     0          9

                      130     FAcility Name     2008-06-30                                                                      C10     0               

                      /CODE]

                      Just to let you know that the accounts with additional lines the info does line up but not in the sample given.

                      Thanks

                      Cindy

                        • Multiple Line Data Trap
                          Grant Perkins

                          Cindy,

                           

                          What I was hoping you would get wouold be something more like this: (if I get the edit about right ...)

                           

                          130     Facility Name    2008-06-30    205389620     Doe ,ALICE    O    R    J    J        2007-11-09    100        0        0    J01    100    0        7

                          130    Facility Name    2008-06-30    204033674 Doexxxxxx ,TARIQ    O    E    7    J        2007-01-19    -501.6    2008-02-11    -1120.6    M    3    W01    0    -501.6    2007-09-27    9   J01    0           

                          130    Facility Name    2008-06-30    203057708    Doe ,LOTTIE    A    S    6    F        2006-07-28    952    2006-08-21    -8928.82    M    0    A26    0    0    2006-08-02    9    Q65    952           

                          130    Facility Name     2008-06-30    205853054    ABI- Doe ,JULIETTE    O    R    B    J        2008-02-18    60    2008-03-10    -116.96        0    B05    60    0        5

                          130    FAcility Name    2008-06-30    203542493    XXXXXXXXX ,JULIA    O    R    M    J        2006-09-20    105.53    2007-04-24    0        0    M99    105.53    0        9   C10    0           

                          /code

                          /quote

                           

                          Looks like your trap is picking each line for the DETAIL but you only want it to pick the Master lines. Can you change it so that it will only select the master lines? I can't really suggest much without the full sample to look at. From the small sample I could suggest just using the first character of a line as a numeric trap but I don't know if this would hold good for the entire report.

                           

                           

                          Grant

                            • Multiple Line Data Trap
                              crazzi _

                              Grant,

                              Are you suggesting that for the file to be exported or working with the original file?? With it being Friday...the brain functions might not be all there at this time. I can't seem to see how to append the additional lines of data to the master since I have only the three code and $$ fields to work with for traps. With either it ends up trapping everything.

                              I have trapped the "Master Line" on my original model and with much slicing and dicing of fields was able to get most of the information. Where I really ran into trouble was when an account's master line ends on a page and the additional data starts on the top of the next page. I'm not sure how to capture that even though I tried various ways within the model and with the advance tab. 

                              Thanks

                              Cindy:)

                                • Multiple Line Data Trap
                                  Grant Perkins

                                  Cindy,

                                   

                                  Either exported or work within the same model if it looks viable - which would be more likely the case where the 'columns' line up well.

                                   

                                  The multi-row field will only work if your trap does not naturally see each 'sometimes additional line' as a detail line.

                                   

                                  For the records that spread over a page header - play with an APPEND Template that you need to identify as a PAGE HEADER template type. You will need to get the number of rows in that template sample to cover the lines that you wish to make 'invisible' to the DETAIL template's analysis of the report but, once you get that right, the number of lines defined in the Page Header template will become 'invisible' to the Detail template's process. (You can still extract data fields from the Page Header template if you need them.)

                                   

                                  That should solve your page change problem PROVIDED the report is consistent when it electronically prints page header headers. Most reports are, but a few are less than consistent. I have no way to make a call about that as far as your reports are concerned.

                                   

                                  HTH.

                                   

                                   

                                  Grant