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-


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.

[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

[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 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 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.)

[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

[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 service date.

[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

[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.

[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.

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

[AmyNRA]

Yes, thanks, Grant, I can't look at it for a while anyway.