Finding matching dimension for min value

[rwunderlich]

I need a little help with this expression. (Example QVW attached). For two tables:

ShipmentId, Destination, Carrier
1, D1, B

Destination, Carrier, Rate
D1, A, 2000
D1, B, 2500
D1, C, 1500
D1, D, 1800

In a chart, I can find the lowest Rate for a ShipmentId with:

=aggr(min(Rate), Destination)

In the same chart, how can I display the Carrier associated with that lowest Rate? Somthing like:

ShipmentId	Destination	Carrier	Rate	Best Rate	Best Carrier
1	D1	B	2500	1500	C

Thanks

[#2]

how about

Only(total {<Rate={$(=Min(Rate))}>} Carrier)

[Miguel_Angel_Baeyens]

Hello Rob,

I'd use

FirstSortedValue(TOTAL Carrier, Rate)

Hope that helps.

[rwunderlich]

Thanks Miguel. That appears to the right track but it doesn't work when I add other destinations. Attached is an expanded example with a second destination. I figured I needed to add additional dimensions into the total but that didn't give the correct result either.

=FirstSortedValue(TOTAL <Destination, Carrier> Carrier, Rate)

[#5]

Rob, I would do it in the script instead, since I can't figure out a way that works for more shipments or destinations. Hence I added the following code within the script:

BestRates:
Load Destination, firstsortedvalue(Carrier,Rate) as BestCarrier, min(Rate) as BestRate
resident Rate
group by Destination;

Regards

[johnw · Accepted solution]

This appears to work:

aggr(firstsortedvalue(Carrier,Rate),Destination)

Edit: On second thought, since multiple carriers can have the same rate, and firstsortedvalue() returns null in that case, I'm not happy with that expression. I'd rather have a list that I can concatenate. I could swear that either I've solved this or seen the solution before...

Edit: Found what I was looking for. Adapting for this situation, it looks like this does the trick, though maybe there's a simpler expression. I'm not seeing it, though.

aggr(concat(aggr(if(rank(-Rate,1,1)=1,Carrier),Destination,Carrier),','),Destination)

[rwunderlich]

Thanks Ivan. I can't do it in the script because in the application I need to allow for selections.

[rwunderlich]

Thanks John. That's just what I was looking for.

I addressed the multiple carrier problem by creating a "CarrierPreference" field of small values (0.1, 0.2, etc) and added them to the Rate in the function. The concept of a preference already existed in the real app.

=aggr(firstsortedvalue(Carrier,Rate+CarrierPreference),Destination)

The idea ported fine to the real application, which has several more dimensions. For the record, attached is the completed example qvw.

Thanks again to all.

[#9]

John, Rob,

This is great stuff....  I utilized John's second method since I ran into the firstsortedvalue null issue John mentioned and got back - for all my cases.   However, is there a way to use the second case where a concatenated list is generated and I can get only the max or min of the concatenated list?  I am trying to use this value in another computation and need a single value.

Thanks in advance for your consideration and if you can respond to this.

D

[johnw]

If you're getting all nulls, I'd guess either your example is different in some important way, or you've made some minor mistake while adapting the expression to your real example.  Either way, it would be difficult to diagnose without seeing your real example.

To get the max or min, just replace concat() with maxstring() or minstring(), and of course remove the second parameter of the concat().  This works fine in Rob's example:

aggr(minstring(aggr(if(rank(-Rate,1,1)=1,Carrier),Destination,Carrier)),Destination)