topic Re: Maxstring Latest Numerical Value (Expression) in QlikView

Maxstring Latest Numerical Value (Expression)

Tue, 23 Aug 2016 07:40:29 GMT

Good day,

How does one maxstring the latest numerical value results based on a date, sounds confusing but basically i want to obtain the latest Rand Value based on the Lastest Date. Below is a list of dates and their values to help explain my issue:

Date Rand Value Transactiontypeid

2016/08/23 R2000 6

2016/07/24 R3000 6

2016/06/15 R1500 6

2016/05/24 R3600 6

now when i use the following expression:

Maxstring({<Transactiontypeid={6}>}Date)

the results returned is 2016/08/23

and when i do the same for the rand value

Maxstring({<Transactiontypeid={6}>}Rand Value)

the result returned is R3600 is incorrect cause the latest Rand Value (based on the latest date) is R2000 and the maxstring expression has gone for the highest value instead.

is there a way around this or another expression i could use or maybe even a fix i can apply to the backend (script)

Re: Maxstring Latest Numerical Value (Expression)

sunny_talwar — Tue, 23 Aug 2016 08:23:26 GMT

May be you need to use FirstSortedValue() function here:

FirstSortedValue({<Transactiontypeid={6}>} [Rand Value], -Date)

Re: Maxstring Latest Numerical Value (Expression)

sunny_talwar — Tue, 23 Aug 2016 08:24:39 GMT

Here the thing to make sure is that the date is actually read as date by QlikView:

Get the Dates Right

Why don’t my dates work?

Re: Maxstring Latest Numerical Value (Expression)

Tue, 23 Aug 2016 09:23:36 GMT

Thank you, it worked with FirstSortedValue,

Re: Maxstring Latest Numerical Value (Expression)

sunny_talwar — Tue, 23 Aug 2016 10:26:33 GMT

Awesome