Show dimension based on minimum measure

[firmographs]

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?  

[msKarthikeyan]

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

[firmographs]

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.  

[tresesco · Accepted solution]

May be like:

Table Dim : Office

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

Exp 2 (Distance) : Min(Distance)