Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
mellerbeck
Creator II
Creator II

Finding Weight Gain using FirstSortedValue when value is null

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

4 Replies
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
MVP
MVP

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
Creator II
Creator II
Author

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)