topic Re: How to get just a single record from set analysis in QlikView

How to get just a single record from set analysis

Thu, 06 Aug 2015 22:54:44 GMT

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

Re: How to get just a single record from set analysis

sunny_talwar — Fri, 07 Aug 2015 01:51:19 GMT

I would recommend using FirstSortedValue() function:

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

Re: How to get just a single record from set analysis

sunny_talwar — Fri, 07 Aug 2015 02:18:24 GMT

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

Re: How to get just a single record from set analysis

Fri, 07 Aug 2015 12:13:48 GMT

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.

Re: How to get just a single record from set analysis

Fri, 07 Aug 2015 12:33:53 GMT

hi,

very helpful

Re: How to get just a single record from set analysis

Anonymous — Fri, 07 Aug 2015 12:36:19 GMT

HI Fred,

Use help section in Qlikview developer.

regards

Neetha

Re: How to get just a single record from set analysis

sunny_talwar — Fri, 07 Aug 2015 12:41:57 GMT

From QlikView's help:

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

HTH

Best,

Sunny