Getting the name of the competitor who has the lowest price

[#1]

Hi everyone

I'm new to QlikView and I have run into what I think should be a simple problem for you more experienced users.

I have a table which consists of the fields: Model, Competitor, Price

Now I want a table to tell me what the lowest price pr. model number is, and what competitor delivers the model at that price.

No problem getting the lowest price pr. model, that was just expression min(Price), but how do I get the name of the customer who supplies the product at that price?

I tried with a second expression: only({<Price={'$(=Money(min(Price)))'}>} Competitor)

This doesn't work, but if I replace $(=Money(min(Price))) with one of the minimum values, the name of the competitor for that specific model will appear.

What am I doing wrong?

[pover]

If Price is a number then the syntax should be

only({$<Price={$(=min(Price))}>} Competitor)

This will work in some cases since set analysis will give you a global minimum price and not a different minimum price per row.  You might also have to look into using the aggr() function to help make the table.  To help you more please post an example of the table you want to generate.

Karl

[#3]

Karls,

Here's the table (see also attached qvw):

Comptetitor	Model	Price
ABC	1	100
DEF	1	125
GHI	1	150
ABC	2	236
DEF	2	234
GHI	2	201
ABC	3	110
DEF	3	124
GHI	3	109

I have created a pivot table to compare the different prices pr. model, that the competitors offer, but now I want a table that simply shows what competitor has the cheapest price for a given model.

In the attached qvw file, the expression min(Price) correctly shows the lowest price for all models Model 1 = 100, Model 2 = 201, Model 3 = 109.

But my problem is that I don't know how to get the Competitors name in their with the lowest price.

I have inserted you suggestion, but as you correctly expected, it displays only ABC for model 1, because 100 is the lowest price in the entire set. I have played a bit with the aggr function, using Model as a dimension in it, but I can't seem to get it nailed.

How should I use the aggr function to accomplish what I want to do?

[#4]

I managed to find an answer in another thread: http://community.qlik.com/message/186214#186214

By placing the competitor name in a dimension instead of an expression, I could get the lowest price pr. competitior by using this expression: If(Price=min(TOTAL <Model> Price),Price)

[pover · Accepted solution]

In the Competitor field put the following expression:

concat(distinct aggr(if(min(Price)=min(total <Model> Price),Competitor),Competitor,Model),', ')

I added the concat() function in case there is a tie between 2 competitor.  In that case you will get a list of the competitors.  The aggr helps to compare the price of each competitor and model with the total min price of each model.

Karl

[#6]

Thanks Karl, it was really helpful also for me!  : )

[#7]

This code works really well.  I applied to an analysis of the "Cheapest Supplier" for Supply Chain purposes and it is looking like this:

(distinct aggr(if(min(MvmtRcvdAmt)=min(total <Material> MvmtRcvdAmt),VendorName),VendorName,Material),', ')

[Anonymous]

Can this be done in script ?i wanted to calculate similar expression but in script because need the get the field as dimensions to allow users to select from drop down as expressions cannot be used as dropdowns