topic Re: Finding Weight Gain using FirstSortedValue when value is null in QlikView

Finding Weight Gain using FirstSortedValue when value is null

mellerbeck — Wed, 03 Jun 2015 15:42:10 GMT

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?

Re: Finding Weight Gain using FirstSortedValue when value is null

sunny_talwar — Wed, 03 Jun 2015 15:46:56 GMT

What would you like to see when PATID = 2?

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

Re: Finding Weight Gain using FirstSortedValue when value is null

swuehl — Wed, 03 Jun 2015 15:47:12 GMT

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)

Re: Finding Weight Gain using FirstSortedValue when value is null

mellerbeck — Wed, 03 Jun 2015 15:49:16 GMT

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

Re: Finding Weight Gain using FirstSortedValue when value is null

sunny_talwar — Wed, 03 Jun 2015 15:49:51 GMT

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)