Most recent result before first and last visit.

[marunio007]

Hello,

I have 2 tables:

Visit:
Load * Inline [
Patient, VisitDate
1,2014-12-18
1,2015-04-09
1,2015-05-14
2,2014-01-24
2,2014-07-25
2,2015-01-23
]
;
ACR:
Load * Inline [
Patient, ACRDate, ACR
1,2014-08-12,24.1
1,2014-12-03,36.5
1,2015-04-02,12.8
1,2015-04-30,11.3
2,2014-01-17,41
2,2014-07-21,43.2
2,2016-01-21,34.4
]

I need a straight table to show first and last visit for each patient with ACR values/dates prior to those visits.

So my dimension is Patient.

Expressions for first and last vistis are easy: Min(VisitDate) and Max(VisitDate).

ACR date prior to last visit = Date(Max(If(ACRDate<=VisitDate,ACRDate)))

ACR prior to last visit = FirstSortedValue(distinct If(ACRDate<=VisitDate,ACR),-If(ACRDate<=VisitDate,ACRDate))

I am having a problem to come up with ACR date and ACR prior to first visit, so the final table would look like this:

Patient	Last Visit	ACR Date prior to Last Visit	ACR prior to Last Visit	First Visit	ACRDate Prior to First Visit	ACR Prior to First Visit
1	2015-05-14	2015-04-30	11.3	2014-12-18	2014-12-03	36.5
2	2015-01-23	2014-07-21	43.2	2014-01-24	2014-01-17	41

Any help will be greatly appreciated.

Attached is qvw.

Marek

[sunny_talwar · Accepted solution]

Try these expressions

Date(Max(VisitDate))

Date(Max(Aggr(If(ACRDate <= Max(VisitDate),ACRDate), Patient, ACRDate)))

FirstSortedValue(ACR, -Aggr(If(ACRDate <= Max(VisitDate), ACRDate), Patient, ACRDate))

Date(Min(VisitDate))

Date(Max(Aggr(If(ACRDate <= Min(VisitDate),ACRDate), Patient, ACRDate)))

FirstSortedValue(ACR, -Aggr(If(ACRDate <= Min(VisitDate), ACRDate), Patient, ACRDate))

[marunio007]

Hi Sunny,

As previously I am amazed at ease you come up with these!

Is there any way of visualizing output of Aggr() function so I can get a better understanding of what's happening?

I have some understanding, but visualization would help me a lot.

Thank you so much!

Marek

[sunny_talwar]

The easiest way to visualize Aggr() function is to create a straight table with Aggr() dimensions as the straight table dimension... for example look here

[marunio007]

This helps tremendously.

Thank you.