Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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))
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
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
This helps tremendously.
Thank you.