Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Getting the name of the competitor who has the lowest price

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?

1 Solution

Accepted Solutions
Luminary
Luminary

Getting the name of the competitor who has the lowest 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

7 Replies
Luminary
Luminary

Getting the name of the competitor who has the lowest price

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

Not applicable

Re: Getting the name of the competitor who has the lowest price

Karls,

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

ComptetitorModelPrice
ABC1100
DEF1125
GHI1150
ABC2236
DEF2234
GHI2201
ABC3110
DEF3124
GHI3109

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?

Not applicable

Re: Getting the name of the competitor who has the lowest price

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)

Luminary
Luminary

Getting the name of the competitor who has the lowest 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

Not applicable

Getting the name of the competitor who has the lowest price

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

Not applicable

Re: Getting the name of the competitor who has the lowest price

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),', ')

ramya_beginner
Contributor

Re: Getting the name of the competitor who has the lowest price

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

Community Browser