Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So, say you have a couple of patients
LOAD PATID, WEIGHTLB, date(CONTACT_DATE, 'YYYY-MM-DD') as CONTACT_DATE INLINE [
PATID, WEIGHTLB, CONTACT_DATE
1, 100, 2015-01-01
1, 120, 2015-02-01
1, 150, 2015-03-01
2, 200, 2015-01-01
2, 220, 2015-02-01
2, , 2015-03-01
3, 200, 2015-01-01
3, 220, 2015-02-01
3, 250, 2015-03-01
];
And you want to calculate weight gain. I'm using something like this
FirstSortedValue(WEIGHTLB, -CONTACT_DATE) // use a minus to get the last value sorted by contact_date
-
FirstSortedValue(WEIGHTLB, CONTACT_DATE)) // no minus gets us the first value sorted by contact_date
/
FirstSortedValue(WEIGHTLB, CONTACT_DATE)
Which works fine, unless of course there is a null value for the first or last value. (Like PATID 2)
Any thoughts on handling this? Maybe a different approach?
Maybe consider only records with WEIGHTLB larger zero
=(FirstSortedValue({<WEIGHTLB = {">0"}>} WEIGHTLB, -CONTACT_DATE) // use a minus to get the last value sorted by contact_date
-
FirstSortedValue({<WEIGHTLB = {">0"}>} WEIGHTLB, CONTACT_DATE)) // no minus gets us the first value sorted by contact_date
/
FirstSortedValue({<WEIGHTLB = {">0"}>} WEIGHTLB, CONTACT_DATE)
What would you like to see when PATID = 2?
Would you want to do second highest date - min date / min date in that case?
Maybe consider only records with WEIGHTLB larger zero
=(FirstSortedValue({<WEIGHTLB = {">0"}>} WEIGHTLB, -CONTACT_DATE) // use a minus to get the last value sorted by contact_date
-
FirstSortedValue({<WEIGHTLB = {">0"}>} WEIGHTLB, CONTACT_DATE)) // no minus gets us the first value sorted by contact_date
/
FirstSortedValue({<WEIGHTLB = {">0"}>} WEIGHTLB, CONTACT_DATE)
Yes, swuehl. I think that will do the trick. You make it seem so easy
If yes then use flag where you do have a value:
LOAD PATID, WEIGHTLB, date(CONTACT_DATE, 'YYYY-MM-DD') as CONTACT_DATE, FLAG INLINE [
PATID, WEIGHTLB, CONTACT_DATE, FLAG
1, 100, 2015-01-01, 1
1, 120, 2015-02-01, 1
1, 150, 2015-03-01, 1
2, 200, 2015-01-01, 1
2, 220, 2015-02-01, 1
2, , 2015-03-01, 0
3, 200, 2015-01-01, 1
3, 220, 2015-02-01, 1
3, 250, 2015-03-01, 1
];
and use this expression:
=(FirstSortedValue({<FLAG = {1}>}WEIGHTLB, -CONTACT_DATE) // use a minus to get the last value sorted by contact_date
-
FirstSortedValue({<FLAG = {1}>}WEIGHTLB, CONTACT_DATE)) // no minus gets us the first value sorted by contact_date
/
FirstSortedValue({<FLAG = {1}>}WEIGHTLB, CONTACT_DATE)