topic Re: Finding Column Name having minimum value in QlikView

Finding Column Name having minimum value

Fri, 05 Oct 2012 14:18:41 GMT

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

Re: Finding Column Name having minimum value

Fri, 05 Oct 2012 14:52:44 GMT

See if it's this.

Re: Finding Column Name having minimum value

Fri, 05 Oct 2012 14:59:46 GMT

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?

Re: Finding Column Name having minimum value

Sat, 06 Oct 2012 08:34:18 GMT

is there anyone who can help me.

Appriciate a lot...

Re: Finding Column Name having minimum value

whiteline — Sat, 06 Oct 2012 09:27:55 GMT

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).

Re: Finding Column Name having minimum value

Sat, 06 Oct 2012 10:03:23 GMT

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.

Re: Finding Column Name having minimum value

Gysbert_Wassenaar — Sat, 06 Oct 2012 10:38:57 GMT

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.

Re: Finding Column Name having minimum value

Mon, 08 Oct 2012 15:24:10 GMT

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.

Re: Finding Column Name having minimum value

whiteline — Mon, 08 Oct 2012 16:16:24 GMT

Try something like this (Product as dimension):

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

Message was edited by: whiteline

Re: Finding Column Name having minimum value

Mon, 08 Oct 2012 16:29:47 GMT

Thanks for reply but above function giving error...