8 Replies Latest reply: May 15, 2014 10:02 AM by AmyNRA _ RSS

    Use data on the last line vs. the first?

    AmyNRA _

      I am trapping demographic account information, then a date from the detail lines.

      I only want to use the date from the LAST date of service.

      If my template is an append, then it gives me the last service date encountered, but it's the date that belongs to the preceding account. 

      If my template is a footer, then it gives me the FIRST service date encountered for the account and ignores values of succeeding lines.

       

      I even tried making it a multi-line field, but I get errors trying to manipulate it because it's too long.

       

      A single account looks like this:

      1547820    Mary Smith       DOB 07/08/00

                 123 Main St      Medicare Insurance

                 City, ST 00000

       

           45  T #   1118 U MARY     E    SMITH           Total:   420.00                          

      03/23/06  ALLEY MD      BLANK                           ALLEY MD OFFICE                      

      031706   99214     -25-OFFICE/OUTPATIE  610.0     -CYST, SOLITARY,  01            130.00    

      031706   19000     -  -ASPIRATION OF B  610.0     -CYST, SOLITARY,  01            290.00    

                040506-040706-BS OF MD FEDERASB580-0011181-Y-N-P cleared                           

                   420.00 /    72.51 /      .00 /      .00 /   263.49                                                                               

      91  T #   2681 U MARY      E   SMITH           Total:   420.00                          

      07/18/06  ALLEY MD      BLANK                           ALLEY MD OFFICE                      

      071006   99214     -25-OFFICE/OUTPATIE  610.0     -CYST, SOLITARY,  01            130.00    

      071006   19000     -  -ASPIRATION OF B  610.0     -CYST, SOLITARY,  01            290.00    

                071906-      -BS OF MD FEDERASB580-0026811-Y-N-P cleared                           

                   420.00 /   103.96 /      .00 /      .00 /   228.14                              

          234  T #   8482 U MARY      E   SMITH           Total:   155.00                         

      05/11/07  ALLEY MD      NEWHOUSE                        ALLEY MD OFFICE                     

      050307   99214     -  -OFFICE/OUTPATIE  610.0     -CYST, SOLITARY,  01            155.00   

      050307             -  -ADDITIONAL DIAG  611.71    -MASTODYNIA       01              0.00   

      050307             -  -ADDITIONAL DIAG  610.1     -MASTOPATHY, DIF  01              0.00   

                051207-      -AETNA          60054-0084821-Y-N-P cleared                          

                   155.00 /    60.00 /      .00 /      .00 /    65.00                             

       

      03/23/06  0001-PMT CASH               

                0001     15.00-             

      04/25/06  0255-PMT BS OF MD FEDERASB580

                0001     72.51-             

      04/25/06  0100-Deductible     69.00   

                0001      0.00              

      04/25/06  0101-Co-ins         15.00   

                0001      0.00              

      04/25/06  0255-W/O BS OF MD FEDERASB58

                0001    263.49-             

      05/16/06  0002-PMT #685   PERSONAL CHEC

                0001     69.00-             

       

      /CODE

      Then the next account starts.

       

      There can be any number of detail records.

       

      I think I'm just going to tell them I can't get that date.

        • Use data on the last line vs. the first?
          Grant Perkins

          Amy,

           

          Lots of dates and no headings to describe the report so I thought I would ask rather than guess ...

           

          What do you see as your detail section?

           

          Are you looking to grab the 05/11/07 date or the 05/16/06 date? Or a different one entirely?

           

           

          Grant

            • Use data on the last line vs. the first?
              AmyNRA _

              Thanks for your reply, Grant.

               

              My detail is the demographic info (which includes the balance way over to the right that I didn't show in my sample).

               

              I'm trying to get that 5/11 date, the last date of service.  (The following info is action on the account, but not services.)

               

              My problem is caused by there not being anything at the end of each account; the next account just begins.  (And I can't figure out how to make that[/I] work for me, either.  Although a new account always starts with a page break, a page break can also happen in the middle of an account.)

               

              When I was trying to use a multi-line field, I was planning to find the last

              "T #", then take the take right after it.  But I got an error saying it was too large.  (I don't remember the exact wording of the error.)  Can you think of a way to do it that[/I] way maybe?

               

              (Of course there's a point of diminishing return, where I have to say it's not worth taking more time on this.)

                • Use data on the last line vs. the first?
                  Grant Perkins

                  Hi Amy,

                   

                  OK, I'm getting a feel for this now.

                   

                  Next I need to know what you have to get as output.

                   

                  Do you need all the information in each detail line and sub-activity block (ie the blocks with the variouos dates) or, perhaps, just the header 'name and address' type data and the date of last service? Or perhaps something between the two suggestions?

                   

                   

                  Grant

                    • Use data on the last line vs. the first?
                      AmyNRA _

                      Yes, in between.

                       

                      I need to get one record for each account, one field being that date of last charge.  I've got everything except that date.  I've only been able to get the first[/I] service date.

                        • Use data on the last line vs. the first?
                          Grant Perkins

                          Amy,

                           

                          I think you will need to make this a 2 pass process at the moment.

                           

                          First pass wouold be to treat the difficult dates as detail records and extract them to a table with date and add the Account Key field.

                           

                          In a summary list the Account Number and Date and sort it in descending order by date. Suppress duplicates on the account number column and then dowahtever you prefer to do to make the result a suitable lookup table format. You could probably use it in that form but you might want to tidy it up a bit first.

                           

                          The result should be a summary with only one occurrence of each Accoun Number. Export it to a file or Excel sheet for use as a lookup table.

                           

                          You main model can now ignore those dates but you will need to add an external lookup to the file you just created using the account identifier as the key for the link and reading in the date that it finds.

                           

                          This is the concept in outline. I have not developed a model for the process so I don't have any more specific point by point instructions to offer at this time. If you are already familiar with lookups you can most likely cover this anyway without my input. Let us know how things work out.

                           

                          HTH.

                           

                           

                          Grant

                            • Use data on the last line vs. the first?
                              AmyNRA _

                              Thanks, Grant.  I didn't think of that.

                               

                              I haven't done lookups yet.  I had that challenged stashed away for later. 

                               

                              I think we'll have to get by with the less-than-perfect extraction for now, just so we can get these files processed, and get to that lookup later, when we can.  It's a triage thing, you know.

                                • Use data on the last line vs. the first?
                                  Grant Perkins

                                  Thanks, Grant. I didn't think of that.

                                   

                                  I haven't done lookups yet. I had that challenged stashed away for later.

                                   

                                  I think we'll have to get by with the less-than-perfect extraction for now, just so we can get these files processed, and get to that lookup later, when we can. It's a triage thing, you know.[/quote]

                                   

                                  Lookup is really easy in this situation since you are using the same original data to create the lookup table keys and then to make the look up link.

                                   

                                  It's when you are trying to connect data from different systems using what seem to be identical lookup keys (if they have them) that life can get more 'interesting'. Here you don't have that problem.

                                   

                                  The process would offer a few options for the approach so a specific documanted approach would work best. I'm a bit short of time to write that up now but if you are interested to try it at this time I will see what I can do later. If your not going to look at it we can leave it until you are ready.

                                   

                                  HTH.

                                   

                                   

                                   

                                  Grant