How to get just a single record from set analysis

[#1]

I have a data model with 3 tables:  Scope, ScopeStatus, and Location where ScopeStatus has a ScopeId and a LocationId which joins the three tables together.  A location can hold multiple scopes.  ScopeStatus also has a TimeEnteredLocation property. 

I want to display only the last Scope that entered the Location.  I have a similar piece of code which displays a list of all of the Scopes at a Location but I can't quite figure out the logic to get only the last Scope that entered the Location.

=Concat({$<[LocationId] = {$(=$(vSoiledRoom1))}, [Scope State] = {"null"}>}[Scope Name], Chr(10))

Thanks,

Fred

[sunny_talwar · Accepted solution]

I would recommend using FirstSortedValue() function:

=FirstSortedValue({$<[LocationId] = {$(=$(vSoiledRoom1))}, [Scope State] = {"null"}>}[Scope Name], -Date)

[sunny_talwar]

I recommended FirstSortedValue with -Date, because that is how I assumed you would find the latest scope. If there is another way to determine latest scope, you might need to replace Date with that field

[#4]

Thanks Sunny T,

This method did the trick.  Is there a reference guide available where I can look up functions like this?  I find bits and pieces but much of the time I do not know the proper phrase to search to get the results I want.

[#5]

hi,

very helpful

[Anonymous]

HI Fred,

Use help section in Qlikview developer.

regards

Neetha

[sunny_talwar]

From QlikView's help:

Other related function attached as a PDF (from QlikView's help also)

HTH

Best,

Sunny