Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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?
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)
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
Thanks Karl, it was really helpful also for me! : )
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),', ')
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