Finding Column Name having minimum value

[#1]

Need help in finding cheapest source for below table.

Product	Supplier01	Supplier02	Supplier03
Apple	10	20	12
Banana	13	15	8
Orange	7	14	7
Grapes	14	10	12

Only three supplier is given but actual data is having more than 100 suppliers for 10000 products.

The output should be as below as last two columns are showing results.

Product	Supplier01	Supplier02	Supplier03	Minimum Price	Cheapest Supplier
Apple	10	20	12	10	Supplier01
Banana	13	15	8	8	Supplier03
Orange	7	14	7	7	Supplier01, Supplier03
Grapes	14	10	12	10	Supplier02

Regards,

Manish Kachhia

[#2]

See if it's this.

[#3]

Thanks for your answer. Looks helpful in case 3 or 4 suppliers. But as I mentioned in my question, we have arond 100 suppliers and it's hectic to use if formula.

Is there any other solution?

[#4]

is there anyone who can help me.

Appriciate a lot...

[whiteline]

Hi.

The easiest ways to solve it osis to transform your data this way:

Product,  Supplier, Price

Apple, Supplier01, 10

Apple, Supplier02, 20

...

Then you could use  Product,  Supplier as dimensions,

and simply =min(Price) for  minimum price and =FirstSortedValue(Supplier, Price) for supplier name.

(read the manual for firstsortedvalue(), as it works if prices are different)

add:

Just use CrossTable() statement before your load statement (look at help).

[#6]

Thanks for your response.

Please check attachment.

I have found solution using if statement, but it’s very problematic  as I have more than 100 supplier and can’t write 100 times IF sentence.

If there any other way to get solution.

Also, in this example, Cheapest Supplier for Orange should be Supplier01, Supplier03 both.

[Gysbert_Wassenaar]

Is your spreadsheet with 100 suppliers in the same format, i.e only one row per product and all suppliers in columns?

You should try to get the data in the form Product, Supplier, Price. One row for every product-supplier-price combination.

Just as Whiteline said above. It's best if you can do that before you load the data in qlikview.

[#8]

I have tried crosstable and firstsortedvalue but it gives supplier name null when the minimum prices shared by two different suppliers.

Let me know what can be done to get both supplier's name in this case.

[whiteline]

Try something like this (Product as dimension):

=Concat({$<Price=min(Price)>} Supplier)

Message was edited by: whiteline

[#10]

Thanks for reply but above function giving error...