FInding most recent date/result

[whimple]

I am attempting to write a report to show the most recent result for several different lab tests for several different patients at my clinic. The information is contained in a table which has the following fields in each record:
- the patient ID number
- the result date
- the result component ID number
- the result value

Each patient can have the same lab component resulted on several different dates, and and has can have several different lab components resulted.

I need to have the report come out like this -

PATIENT Comp. A Value Comp A Date Comp B Value Comp B Date

(each line showing the pat ID, the lastest date and value for component A, lastest date and value for component b, and so on. If the patient never had the component resulted, the date/value columns for that component should be blank).

Any suggestions?

 

[chelseatech]

Yes. Group your date by PatientID (Group1)
Group your data by Result Component ID number (Group2)

The last result for each group is filtered out if you do a GROUP SELECTION formula of
{table.datefield}=
maximum({table.datefield},{table.result comp id})

the rest is just formatting. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[whimple]

The maximum function was my first thought - however, I don't think this will work when I need more than one component ID on the same line, behind the patient's ID - -

patient id Hgb date Hgb result Glucose date Glucose result HDL date HDL result

- where the date is the most recent date that the component was tested, and the result is the result for that most recent date. Each component can have a different most recent date.

Is there any way to have the maximum function return a result based on the data in the field? Say - maximum(Table.datefield,table.componentID) where table.componentid = 174. Is this possible?

 

[kenhamady]

You mentioned 'A, B and so on' ...
Is there a fixed number of columns? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[whimple]

yes - in the end, there should be 10 columns - a date and result column for 5 different components.
In reference to chelseatech's answer - which fields are in the details section?
In the first group?
In the second group?

 

[kenhamady]

Getting the 10 dates would be easy:

Write a formula that says:

If Code = 'a' then {Date.field}

You would do 10 of these formulas and do the max of each. That would give you the last 10 dates, side by side.

The problem is getting the 10 results that go with each of these, and putting them all on one line. To do this you will probably need 10 formulas with a few variables, or a few variable arrays. How handy are you with Crystal variables?
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[whimple]

I'm sorry, Ken - I guess I have not been clear on what I need -
I need a report that will look like this -
Patient Name A1CDate A1CResult GlucoseDate GlucoseResult
Jones, Joe 12/12/01 6.5 1/7/02 75.4
Smith,Mary 11/21/00 7.0 8/9/01 85.9

I can easily produce a report that will show the latest date and result for ONE component - say, the A1C - by putting the result date and result value fields in the detail and group footer sections, group by patient, sort the result date descending, using the component id in the group selection formula, and suppressing the detail and group header sections. It is getting more than one result component and it's latest date on the same line that is the difficulty.
If an array is the way to go, I can learn how - I just need some general direction on if this whole idea is possible and what general strategy is needed.

Thanks for everyone's input!

 

[MalcolmW]

A simple solution is just to use a conditional running total to get the max of the date for each patient ID, using the component ID as a condition. Repeat for various different component ID's.
Sorry if that is a bit cryptic - it's my bedtime. Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[kenhamady]

Actually, you were very clear.

Malcolm's approach is just like the one I suggested. It will give you the max date, but not the result for that date. This would also be simple if they could be one after the other, instead of side by side.

What you will have to do is write a formula that stores a variable that is the date of the first record, then compares each one and replaces the max date when it encounters one higher. You will also replace the result each time you get to a higher date. You will need a separate pair of variables for each type of test you are doing.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com