Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Manish Kachhia
See if it's this.
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?
is there anyone who can help me.
Appriciate a lot...
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).
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.
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.
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.
Try something like this (Product as dimension):
=Concat({$<Price=min(Price)>} Supplier)
Message was edited by: whiteline
Thanks for reply but above function giving error...