topic Re: Finding the newest value in QlikView

Finding the newest value

Mon, 30 Apr 2012 08:39:21 GMT

Hi,

I have a simple table consisting of 3 fields (Container, Status and reload date). Every day the table is expanded with an extra day.

I would like to make a object that shows me the newest status for each container.

See attached file for details.

The result should be:

Container Status

A 1

B 3

C 4

Thanks.

Jakob Hjorth-Hansen

Re: Finding the newest value

SunilChauhan — Mon, 30 Apr 2012 09:04:29 GMT

Create a variable

vlastReload=max([Reload date]) in control+alt+v

in expression you can write below

maxstring(if([Reload date]=vlastReload,Status))

see the attached file

hope this helps

Finding the newest value

jagannalla — Mon, 30 Apr 2012 09:28:36 GMT

Hello,

You can try this expression.

=max({<[Reload date]={"=MaxString([Reload date])"}>} Status)

Finding the newest value

Mon, 30 Apr 2012 11:26:28 GMT

Hi Jagan,

Thank you for the answer but your formula doesn't give me the correct answer.

I expect:

A 1

B 3

C 4

but I get

A 2

B 3

B 4

seems like it takes the highest value?

/Jakob

Re: Finding the newest value

Mon, 30 Apr 2012 11:31:11 GMT

Hi Sunil,

Thank you for the answer - it works, but I was not specific enough.

There can be a situation where the container is no longer in the list, but I still want to see the value from when it was uploaded last time.

I have added a container D to my example, where the last update was on the 28th. I still need to see it on my list with status 2.

Thanks for your interest.

BR.

Jakob

Re: Finding the newest value

jagannalla — Mon, 30 Apr 2012 12:16:08 GMT

Ok according to sunil example. You want to see D value as null even there is no data for that at max date . If this in the case you need uncheck Supress zero values in Presentation tab.

Re: Finding the newest value

IAMDV — Tue, 01 May 2012 15:21:50 GMT

Jakob - Instead of following Set Analysis approach you can solve this by using FirstSortedValue function. Here is what you need...

FirstSortedValue( Status, -[Reload date])

I have used the minus sign before [Reload date] to sort in desc order or last sorted value instead of first sorted value.

I understand itss hard - not to think of Set Analysis but sometimes we need to step back to get things working.

I hope this helps!

Cheers,

www.QlikShare.com

Re: Finding the newest value

Tue, 01 May 2012 18:25:46 GMT

Hi DV,

Simple and easy. Thanks.

/Jakob

Re: Finding the newest value

IAMDV — Tue, 01 May 2012 18:37:16 GMT

Jakob - Very soon, I'm going to make a video post on FirstSortedValue. Is it okay to use this example for demonstration?

Thanks,

www.QlikShare.com

Re: Finding the newest value

Tue, 01 May 2012 18:43:58 GMT

No problem.

Re: Finding the newest value

jagannalla — Wed, 02 May 2012 08:48:44 GMT

D V,

Your blog is really good. Today i learned the concept of FirstSortedValue, Thanks to share with us.

Re: Finding the newest value

IAMDV — Wed, 02 May 2012 08:55:52 GMT

Jagan,

Thank you for the kind words. I'm glad you have learnt something new. Please can you email me directly at dvadithala@gmail.com.

Cheers - DV

Re: Finding the newest value

Wed, 02 May 2012 11:02:13 GMT

I have seen your site many times and I have learnt so much from there. what a Gr8 work you are doing

thanks to sharing

- IndianQvLover

Re: Finding the newest value

IAMDV — Tue, 22 May 2012 10:58:00 GMT

Jakob & All - I understand that we have already got this working by FirstSortedValue() function. But I thought it will be good to use Set Analysis with Indirect Analysis.

MaxString({<[Reload date] = (P({<Status ={$(=max(Status))} >}))>} Status)

Cheers,