Finding Weight Gain using FirstSortedValue when value is null

[mellerbeck]

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?

[sunny_talwar]

What would you like to see when PATID = 2?

Would you want to do second highest date - min date / min date in that case?

[swuehl · Accepted solution]

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)

[mellerbeck]

Yes, swuehl. I think that will do the trick. You make it seem so easy

[sunny_talwar]

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)