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

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST 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)