topic Display a value associated with a min value in QlikView

Display a value associated with a min value

Fri, 23 Oct 2009 22:40:35 GMT

Hi,

I found the same question in the forum archives (by pcorona on må, jul 6 2009). I tried the recommendations, but couldn't get them to work...so I'm posting the same:

Example:

I have 2 tables with a one to many relationship based on "PatientID". Each unique "PatientID" has many records, each dated with "ApptDate" and other fields, such as "DoctorID" (which can change from record to record for any given patient).

How do I display the "DoctorID" associated with the earliest "ApptID" for a given "PatientID" (so, the doctor that was first ever to see a patient) wtihin a chart/pivot table (with "PatientID" as the dimension)?

My guess:

=if(ApptDate=min(ApptDate),DoctorID)

...this is not working (it only displays "-", however, if there is only one record for the patient, then it works), along with many other variations of the above expression based on similar logic.

What am I doing wrong?

Thanks...

Display a value associated with a min value

Fri, 23 Oct 2009 23:03:04 GMT

Hello Nathanael

I think you need to use the AGGR function around your expression, something like:

=IF(ApptDate = Min ( Aggr ( ApptDate , PatientID ) ) , DoctorID )

I may have got the syntax a little wrong, because its the end of the day on Friday and my brain is frankly fried! If this doesn't work then post an example QVW with a bit of sample data and I'll take a look for you on Monday.

Have a great weekend,

Display a value associated with a min value

Wed, 02 Jun 2010 16:27:49 GMT

Hello,

Have you managed to complete your script?