Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Show dimension based on minimum measure

We have an table of Places and Distances, where the distance is the travel distance from various Offices to those Places.  

Table 1:  Offices

Office A

Office B

 

Table 2:  Office, Place, Distance 

Office A, Place 1, 5

Office A, Place 2, 15

Office B, Place 1, 6

Office B, Place 2, 12

 

Distance is calculated from every Office to every Place.  The table we want will have Office, the nearest Place, and min(Distance).  Only 1 row per Office.  

Office A, Place 1, 5

Office B, Place 1, 6

 

How can we approach this in Qlik?  Is it possible either in the load script or in creating a table with calculated dimensions?  

Labels (3)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

May be like:

Table Dim : Office

Exp 1 (Place) : FirstSortedValue(Place, -Distance)

Exp 2 (Distance) : Min(Distance)

 

View solution in original post

3 Replies
Highlighted
Employee
Employee

Hi, 

Should the output be 

Office A, Place 1, 5

Office B, Place 2, 12

or 

Office A, Place 1, 5

Office B, Place 1, 6 

-karthik

Highlighted
Partner
Partner

Karthik, you are correct thank you.  We want the closest Place to every office, and Place 1 is closest to every office.  I've updated the original post.  

Highlighted
MVP
MVP

May be like:

Table Dim : Office

Exp 1 (Place) : FirstSortedValue(Place, -Distance)

Exp 2 (Distance) : Min(Distance)

 

View solution in original post