topic Need Last Value in QlikView

Need Last Value

johnca — Thu, 17 Jan 2013 00:13:54 GMT

Here's an easy one, but to me it's not so easy <sigh>, especially when the boss needs it yesterday.

I have ship data where a widget can ship and be returned and ship again. My issue is, I need to find the last time a widget shipped prior to a certain date (got this part). I was able to add a count number to each record as shown in the table below, but can't seem to eliminate all but the highest counter, or latest date. I was hoping to find something like LastSortedValue, but alas...not to be.

Widget SerNo	Ship Date	Counter
1	1/1/2011	1
1	4/4/2011	2
2	1/2/2011	1
3	3/5/2011	1
4	2/1/2011	1
4	4/1/2011	2
5	1/1/2011	1
5	2/1/2011	2
5	3/1/2011	3
6	3/5/2011	1

I need my data to look like this:

Widget SerNo	Ship Date	Counter
1	4/4/2011	2
2	1/2/2011	1
3	3/5/2011	1
4	4/1/2011	2
5	3/1/2011	3
6	3/5/2011	1

Thanks,

John

Re: Need Last Value

danielrozental — Thu, 17 Jan 2013 02:21:17 GMT

You can use firstsortedvalue with a reversed order.

Ship date expression would be, firstsortedvalue([Ship date],-[Ship date]) and counter firstsortedvalue(Counter,-[Ship date]).

Re: Need Last Value

er_mohit — Thu, 17 Jan 2013 05:56:17 GMT

use this function

-firstsorted value function.

it gives you last value..

Re: Need Last Value

johnca — Thu, 17 Jan 2013 15:27:01 GMT

I had a feeling it would be FirstSortedValue-something-or-other. I see the '-' sign designates the last record rather than the first record...perfect.

Thanks Daniel

Re: Need Last Value

johnca — Thu, 17 Jan 2013 15:30:57 GMT

This seems to return a negative value of what I'm seeking, date or counter.