topic Finding matching dimension for min value in QlikView

Finding matching dimension for min value

rwunderlich — Thu, 03 Mar 2011 20:32:43 GMT

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

Finding matching dimension for min value

Thu, 03 Mar 2011 20:39:01 GMT

how about

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

Finding matching dimension for min value

Miguel_Angel_Baeyens — Thu, 03 Mar 2011 20:41:16 GMT

Hello Rob,

I'd use

FirstSortedValue(TOTAL Carrier, Rate)

Hope that helps.

Finding matching dimension for min value

rwunderlich — Thu, 03 Mar 2011 21:27:55 GMT

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)

Finding matching dimension for min value

Thu, 03 Mar 2011 21:33:27 GMT

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

Finding matching dimension for min value

johnw — Thu, 03 Mar 2011 21:47:37 GMT

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)

Finding matching dimension for min value

rwunderlich — Thu, 03 Mar 2011 21:49:22 GMT

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

Finding matching dimension for min value

rwunderlich — Thu, 03 Mar 2011 23:44:58 GMT

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.

Finding matching dimension for min value

Mon, 29 Aug 2011 20:45:58 GMT

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.

Re: Finding matching dimension for min value

johnw — Mon, 29 Aug 2011 22:37:48 GMT

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)

Re: Finding matching dimension for min value

Mon, 29 Aug 2011 23:05:33 GMT

Thanks John…

That’s a little simpler than computing it as I did.

Don