topic How to look-up a value, peek? in QlikView

How to look-up a value, peek?

Wed, 25 Nov 2020 16:16:04 GMT

Using example data:

MovieStats:
Load MovieTitle,
Min(RTS) As MinScore,
Max(RTS) As MaxScore
Resident Movie
;

How could I look up and display the MovieTitle for both MinScore and MaxScore?

I have tried to implement Peek() but it does not seem like the correct approach. For purposes of setting a Refrence Line Expression on Line Graph.

Thx

swuehl — Thu, 18 May 2017 21:04:30 GMT

Using FirstSortedValue:

MovieStats:
Load

Firstsortedvalue(MovieTitle, RTS) as MinTitle,

Firstsortedvalue(MovieTitle, -RTS) as MaxTitle,

Min(RTS) As MinScore,
Max(RTS) As MaxScore
Resident Movie
;

Fri, 19 May 2017 11:56:59 GMT

This works, in case where there is only one value.

For example, if multiple records have an RTS of 100, how could I provide a list? Or maybe just sort and provide the 1st instance?

Thanks!

swuehl — Fri, 19 May 2017 12:15:05 GMT

For a list:

MovieStats:

JOIN (Movie)

Load
Min(RTS) As MinScore,
Max(RTS) As MaxScore
Resident Movie
;

MovieResult:

LOAD *,

If(RTS = MinScore, MovieTitle) as MinTitleList,

If(RTS =MaxScore, MovieTitle) as MaxTitleList

RESIDENT Movie;

DROP TABLE Movie;

Or, a pure front end solution like

=Concat({<RTS = {$(=Max({1} RTS))}>} DISTINCT MovieTitle,', ')

=Concat({<RTS = {$(=Min({1} RTS))}>} DISTINCT MovieTitle,', ')